Filter Report by Form

  • Thread starter Thread starter imelda1ab
  • Start date Start date
I

imelda1ab

I'm using the following code from the wonderful Allen Browne which
works perfectly in an either or scenario (either by Start/End Date(s)
OR filtering by client name; but I don't know what syntax to use to
filter by Start/End date(s) AND anything typed in the client name
field. I'm sure it's easy but I can't get my head around it. I
appreciate any and all assistance!!

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of
this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the
field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strClientField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match
your local settings.

'DO set the values in the next 3 lines.
strReport = "rptReadingFile" 'Put your report name in these
quotes.
strDateField = "[Date]" 'Put your field name in the square
brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of
preview.


'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format
(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format
(Me.txtEndDate + 1, strcJetDate) & ")"
End If


'Client field
If Not IsNull(Me.txtClientName) Then
strWhere = "([Client] Like ""*" & Me.txtClientName & "*"")"
End If



'Close the report if already open: otherwise it won't filter
properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the
start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
 
There is more helpful advice from Allen on how to do this.
Look at his sample database for searching.
It shows how to build the filter string from several search boxes.
You can use that filter string as strWhere to open your report.

here's the link
http://www.allenbrowne.com//ser-62.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
There is more helpful advice from Allen on how to do this.
Look at his sample database for searching.
It shows how to build the filter string from several search boxes.
You can use that filter string as strWhere to open your report.

here's the link
http://www.allenbrowne.com//ser-62.html
Click to expand...

Yes, and for another approach you can have a report prompt the user
for criteria before it runs. Open a form from the report's Open
event, using Dialog mode so that the report waits for the form to be
closed or hidden before it proceeds. That way you can collect
criteria from the user and build a Where clause for the report. It
also means that you can call the report directly - you don't need to
call it from a form. And the selection form is reusable - it can be
called from multiple reports if they need the same criteria.

I've posted examples of this technique on our free J Street Downloads
page at http://ow.ly/M58Y
See "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top