Multiple filters

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

Ok, I got help this morning with how to type the applyfilter in my code.
The problem is, I have multiple things I would like to filter on and each
time I apply a new filter it wipes out the previous one. For example:

My form lists all records in our assignment table. If I enter a userid in
my 'finduserid' field, I can then applya filter to show only records for
that employee. I may also want to only see incomplete assignments. I
created two buttons that allows the user to select "Open Assignments" or
"All Assignments". I also have a field that allos me to select specific
clients and filter for assignemtns for the particular client. I have
figured out how to apply filters to make each of these work by themselves,
but how do I make them work together.

If I want to see all the incomplete assignements that John doe has in his
queue for the XYZ Company, how can I do it?

Thanks!! AGAIN


Private Sub ShowAll_Click()
DoCmd.ApplyFilter , "DateCompleted = date() or not(datecompleted =
date()) or datecompleted is null"
End Sub

Private Sub ShowWIP_Click()
DoCmd.ApplyFilter , "DateCompleted is null Or month([datecompleted]) =
month(now()) and Year([datecompleted]) = year(now())"
End Sub

Private Sub Refresh_Click()
If Not FindSupervisor = "" Then
DoCmd.ApplyFilter "SupervisorID = [FindSupervisor])"
End If

If Not [FindClient] = "" Then
DoCmd.ApplyFilter "Client# = [FindClient]"

If Not [FindUser] = "" Then
DoCmd.ApplyFilter "UserID = [FindUser]"
End If
End Sub
 
You can combine statements to make filter strings work on
more than one field.

Me.Filter = "[UserID] = " & lngUserID & " AND [ClientID]
= " & lngClientID & " AND [Completed] = True"

Obviously, replace my field names with yours and my
variables with yours (or if you're referencing your
controls directly, don't use variables). You may want to
program the logic to check which criteria controls have
values and build the filter statement accordingly. In
other words, if there is a value in the User control and
Client control (you're looking for assignments for a
particular user and for a particular client), then build
the filter string to include those fields, but don't
include anything for the completed field.

I usually use a string variable to build the filter string.

Dim strFilter as String

strFilter = "[UserID] = " & finduserid
If not isnull(findclient) then
strfilter = strfilter & " AND [ClientID] = " &
findclient
End if
 
Back
Top