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

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

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

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

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

mmcdonal

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

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

mmcdonal

    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]

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

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