Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 6th, 2006, 08:56 AM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple criteria for a Report

Hey guys,

I'm trying to make a report that lets the user select(from a form) which rows get displayed from the table.
The form has two comboboxes for the begin date and the end date, which come from the Req_Date field. The form also has two check boxes(twenty and forty). twenty is a field as is forty. The value in these two fields is either "Y" or "N". One has to be "Y" and the other has to be "N". The user needs to be able to select one or both check boxes.

One question I have is; do I need to put the two checkboxes in a group or leave them single?
I'm also not sure how to tie the check boxes to the table/fields.
Below is the code I have so far. It works for getting the rows that fall between the selected dates, but I'm not sure how to tie the check boxes in there. I'm thinking a nested if statement or a Select Case, but I'm not sure how to go about it.

    Dim strBegin As String
    Dim strEnd As String
    Dim strDoc As String
    Dim strWhere As String
    Dim strJitl As String

    strDoc = "Floor Requests"

If Len(Me.cboStartDate & "") > 0 And Len(Me.cboEndDate & "") > 0 Then
    ' entries in both
    strWhere = strWhere & " AND Req_Date Between #" & Me.cboStartDate & "# AND #" & Me.cboEndDate & "#"
Else
    MsgBox """Select Valid Date Range""", vbOKOnly
End If

If Len(strWhere & "") = 0 Then
    DoCmd.OpenReport strDoc, acViewPreview
Else
    DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=Mid(strWhere, 6)
End If

Thanks for any help,

Dave

  #2 (permalink)  
Old June 6th, 2006, 10:39 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Start with the query. In the criteria, refer to the combo boxes like this for the date range:

Between [Forms]![frmYourFormName].[cboYourComboBoxName1] And [Forms]![frmYourFormName].[cboYourComboBoxName2]

Then take the check box values and pass them like this:

Dim sCheck20 As String
Dim sCheck40 As String

If Me.Check20 = -1 Then
   sCheck20 = "Yes"
   Else
   sCheck20 = "No"
End If
If Me.Check40 = -1 Then
   sCheck40 = "Yes"
   Else
   sCheck40 = "No"
End If


Then:

sLink = "[20] = " & "'" & sCheck20 & "' AND [40] = " & "'" & sCheck40 & "'"

Then:

DoCmd.OpenReport sDocName, , sLink 'in where comma

Modify as needed for error checking etc.

Does this help?




mmcdonal
  #3 (permalink)  
Old June 6th, 2006, 10:40 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

In answer to your other question, you can make the check boxes mutually exclusive by using radio buttons in a group, or you can create a combo box or list box with 20 and 40 in the list, and only let the user select one.

HTH

mmcdonal
  #4 (permalink)  
Old June 6th, 2006, 12:41 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks mmcdonal

I plugged in your code and changed the variables, but when I run it and make the inputs they don't do anything. All records are still brought up in the report.

Any ideas?

Thanks
-Dave

  #5 (permalink)  
Old June 6th, 2006, 12:43 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Did you use the exact code, or modify it to match the control and form names in your database?

mmcdonal
  #6 (permalink)  
Old June 6th, 2006, 01:02 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I changed the form name and combobox names for the query.
And I changed the checkbox names and document names in the vb code.
I also declared these variables:

    Dim sCheck20 As String
    Dim sCheck40 As String
    Dim sLink As String

  #7 (permalink)  
Old June 6th, 2006, 01:05 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Can you post the code you used after all modifications, and the SQL statement from the query?

mmcdonal
  #8 (permalink)  
Old June 6th, 2006, 01:12 PM
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

    Dim strDoc As String
    Dim strDate As String
    Dim sCheck20 As String
    Dim sCheck40 As String
    Dim sLink As String

    strDoc = "Floor Requests"
If Me.chkTwenty = -1 Then
   sCheck20 = "Yes"
   Else
   sCheck20 = "No"
End If
If Me.chkForty = -1 Then
   sCheck40 = "Yes"
   Else
   sCheck40 = "No"
End If
sLink = "[20] = " & "'" & sCheck20 & "' AND [40] = " & "'" & sCheck40 & "'"
DoCmd.OpenReport strDoc, acViewPreview, sLink

-This is in Criteria row of Req_Date Column-

Between [Forms]![frm_Report].[cboStartDate] And [Forms]![frm_Report].[cboEndDate]

  #9 (permalink)  
Old June 6th, 2006, 01:16 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

First of all, the OpenReport syntax is:

DoCmd.OpenReport stDocName, acViewPreview, , sLink

Please note the position of the sLink and the commas after acViewPreview. sLink needs to be in the Where section. You have it in another section.

More in a few...


mmcdonal
  #10 (permalink)  
Old June 6th, 2006, 01:18 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Please do some sleuthing here and insert this code right before the DoCmd.OpenReport:

MsgBox sCheck20 & " " & sCheck40 & vbCrLf & sLink

This way you can check to see if the code is working for these issues.



mmcdonal




Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple criteria on a select query? jihadbuster XSLT 4 March 19th, 2008 01:52 PM
IIF multiple criteria stealthdevil Access VBA 10 November 28th, 2007 11:37 AM
Multiple Search Criteria in a Form tet Access VBA 5 October 27th, 2007 08:54 AM
stLinkCriteria - Multiple Criteria andyv123 Access 1 September 8th, 2006 10:02 AM
Criteria query with multiple fields penta Access 8 October 25th, 2004 11:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.