Let's say you want to make form called
FilterForm to filter data on a form called
DataForm.
You want to be able to filter
DataForm for the following fields:
[Start Date] between two specified dates
[Finish Date] after a specified date
[User ID] equal to a specified user ID
You would make your
FilterForm have four fields:
[StartRange1]
[StartRange2]
[FinishAfter]
[User ID Number]
All of these fields would be
unbound
Also on your
FilterForm you would have a button called
Apply Filter
The following code would be attached to that button:
Code:
dim strFilter as String 'Used to build a filter
strFilter = "" 'Start with a blank filter
'Verify the both ends of the start date range are valid
If (IsDate(Me![StartRage1]) && IsDate(Me![StartRange2])) then
'Build the filter
strFilter = "([Start Date] between #" _
& Me![StartRange1] & "# AND #" _
& Me![StartRange2] & "#)"
'Verify the finish after date is valid
If (IsDate(Me![FinishAfter]) Then
'If the filter is not blank, then AND this criteria
If (Len(strFilter) > 0)
strFilter = strFilter + " AND "
End If
'Add this criteria
strFilter = strFilter & ([Finish Date] > #" _
& Me![FinishAfter] & "#)"
End If
'Verify the user id number is not blank (do more validation if required)
If (Not IsNull(Me![User ID Number])) Then
'If the filter is not blank, then AND this criteria
If (Len(strFilter) > 0)
strFilter = strFilter + " AND "
End If
'Add this criteria
strFilter = strFilter & ([User ID] = " _
& Str(me![User ID Number]) & ")"
End If
If Len(strFilter) Then
[Forms]![DataForm].Filter = strFilter
[Forms]![DataForm].FilterOn = True
End If
Obviously these fields and button could be on the original form. When I've used this in the past, I use a checkbox on the original form called
Display Filter When the checkbox is checked, the
FilterForm is opened, when unchecked it is closed. This way the filter fields are not cluttering up the form when not in use. There are lots of variations on this type of theme.
