INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

User Interface Techniques

How do I let the user create a dynamic filter for my form? by jflachman
Posted: 31 May 01

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:
 

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.

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close