OpenReport with criteria entered on an unbound form

[Gina K]

I'm currently using an unbound form with text boxes and a button to open a
report in Access2000 (very new at this, by the way):

DoCmd.OpenReport "rptMeet", acPreview, "", ("[MeetDate]=
Forms![frmPrintDialog]![txtMeetDate]")

I'd like to include two additional text boxes in which to enter criteria,
School1 and School2. If the user enters data in the boxes, I'd like the
report to filter using that data (the user would be entering data in both
fields); however, I also want the option of leaving those 2 fields blank and
having the report filter only on the txtMeetDate from the example above.

Thanks for the assist!

 

[Graham Mandeno]

Hi Gina

I assume that this code is in the class module of the form with the text
boxes and the button?

Use some slightly more complex code to construct a filter string. I'm not
sure how you want School1 and School2 to affect the filter, but this should
give you enough to go on:

Dim sFilter as String
sFilter = "([MeetDate]=" & Format( txtMeetDate, "\#mm/dd/yyyy\#" ) & ")"
If Not IsNull( txtSchool1 ) Then
sFilter = sFilter & " and ([School1]='" & txtSchool1 & "')"
End If
If Not IsNull( txtSchool2 ) Then
sFilter = sFilter & " and ([School2]='" & txtSchool2 & "')"
End If
DoCmd.OpenReport "rptMeet", acPreview, , sFilter