Filter report by Form with more than one criteria (

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I thought this might be helpful to other users as it took me a while to get
it to work.
This will on the click of a button filter a report by criteria in a form,
for a start date, end date, and two others of your choosing
 
Sorry Pressed the wrong button on the key board.
Heres the code.


Private Sub OK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#dd\/mm\/yyyy\#"

strReport = "Transaction Report" 'Name of your Report
strField1 = "[Transaction date]" 'Name Of date field
strField2 = "[Asset ID]" 'Name of criteria 2
strField3 = "[MSISDN]" 'Name of criteria 3
'Date criteria section
If IsNull(Me.[start Date]) Then
If Not IsNull(Me.[End Date]) Then 'End date, but no start.
strWhere = strField1 & " < " & Format(Me.[End Date],
conDateFormat)
End If
Else
If IsNull(Me.[End Date]) Then 'Start date, but no End.
strWhere = strField1 & " > " & Format(Me.[start Date],
conDateFormat)
Else 'Both start and end dates.
strWhere = strField1 & " Between " & Format(Me.[start Date],
conDateFormat) _
& " And " & Format(Me.[End Date], conDateFormat)
End If
End If
'All Other Criteria sections.
'NOTE: if only one extra criteria is needed take out the argument after
the "ELSE" command
'Also This only works for one or the other criteria
If IsNull(Me.IMEI) Then 'If criteria 2 is blank
If Not IsNull(Me.MSISDN) Then 'This will only run if there is
nothing in the first box.
strWhere = strField3 & " = " & Me.MSISDN 'Sets Where clause in
the report to the setting for String 3
End If
Else
If IsNull(Me.MSISDN) Then 'This runs if the previous criteria did
not
strWhere = strField2 & " = " & Me.IMEI
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere 'This opens your
report using all the above data
End Sub

If you wish to force users to use both criteria, in my coding the 2nd and
3rd strings, (and the dates) at the same time then replace the second If ----
Else ----- End If clause with:


If Not IsNull(Me.IMEI) Then 'If criteria 1 is not blank
If Not IsNull(Me.MSISDN) Then 'This will only run if there is
something in the first box.
strWhere = strField3 & " = " & Me.MSISDN 'Sets Where clause in
the report to the setting for String 3
End If
Else
If Not IsNull(Me.MSISDN) Then 'This runs if the previous criteria
did
strWhere = strField2 & " = " & Me.IMEI
End If
End If


For the use of one or the other, i.e. only one or both fields can be used:

If Not IsNull(Me.MSISDN) Then 'If criteria 2 is not blank
If Not IsNull(Me.MSISDN) Then 'This will only run if there is
something in the second criteria
strWhere = strField3 & " = " & Me.MSISDN 'Sets Where clause in
the report to the setting for String 3
End If
Else
If Not IsNull(Me.IMEI) Then 'This runs regardless
strWhere = strField2 & " = " & Me.IMEI
End If
End If
 
Back
Top