'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
'Note: Only records matching ALL of the criteria are returned.
'Author: Allen Browne (allen@allenbrowne.com), June 2006.
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbCity) Then
strWhere = strWhere & "([City] = """ & Me.cmbCity & """) AND "
End If
If Not IsNull(Me.cmbCategory) Then
strWhere = strWhere & "([Category type] = """ & Me.cmbCategory & """) AND "
End If
If Not IsNull(Me.cmbLocation) Then
strWhere = strWhere & "([Publication Location] Like ""*" & Me.[cmbLocation] & "*"") AND "
End If
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
'Apply Filter to Report
DoCmd.OpenReport "rMediaContacts", acViewPreview, , strWhere
DoCmd.Maximize
Reports![rMediaContacts].Filter = strWhere
Reports![rMediaContacts].FilterOn = True
End If