Multi Filter on Form

[PlasticGuy]

Hello,

I have tried searching this and other forums, but I cannot seem to get the code correct.

I have a continuous form based on a query of multiple tables. There are two text fields on the form that are based on text data from fields in one of the tables on the query.

I have a series of option buttons that when clicked will filter the form based on a certain value (stored as a string value in the code) that may be in one of those fields for the record. (Example below uses two of the source fields)

I have no problem with this and I can get the form to filter the records just fine when a single option button is checked.
Example: both of these filter functions work fine

Me.Filter = "[Document Type] = '" & strform & "'"
Me.FilterOn = True

Me.Filter = "[Department] = '" & strtraffic & "'"
Me.FilterOn = True


The problem I have is when trying to filter when there is more than one option button checked at a time. I have no issue with the conditional logic on having one or more boxes checked, it is the actual multi filter code that does not work.

I have tried
Me.Filter = "[Document Type] = '" & strform & "'" And "[Department] = '" & strtraffic & "'"

I get runtime error 13 Type mismatch error

I have tried other variations that do not work at all.

Any help would be appreciated.

Thanks,

 

[pr2-eugin]

Hello PlasticGuy, Welcome to AWF..

Try this..

Me.Filter = "[Document Type] = '" & strform & "' And [Department] = '" & strtraffic & "'"

Are Document Type and Department Text type?

 

[PlasticGuy]

Yes they are and yes that solved it!

Thank you for the quick response. I really struggled with that for some reason.

 

[pr2-eugin]

The problem you had was unwanted double Quotes before the AND keyword and another one before the word Department... Glad to help..

 

[andrewbc]

Hi

I have a similar problem with my form. Single Filters work OK but would like to include ApptDate in the Filter as well.

Text9 is a Combo box with list of Drivers which returns a numerical value. If this Combo is empty it needs to show all drivers

Text11 is a Combo box which returns a numerical value. If this combo is empty it needs to show all appt types

Tex13 is a Date box where staff can choose the date they wish to view. When i try and combine both crieteria i get a run time 13 error Type Mismatch.

Private Sub Command59_Click()
' Driver Search
Me.Filter = "[Driver In] Like " & Chr(34) & Me.Text9 & "" & Chr(34)
Me.FilterOn = True
Me.Requery
End Sub

Private Sub Command60_Click()
'Appt Type Search
Me.Filter = "[Appt TypeID] Like " & Chr(34) & Me.Text11 & "" & Chr(34)
Me.FilterOn = True
Me.Requery
End Sub

Private Sub Command61_Click()
'Date Search
Me.Filter = "[ApptDate] Like " & Chr(34) & Me.Text13 & Chr(34)
Me.FilterOn = True
Me.Requery
End Sub


New code i am trying

Private Sub Command59_Click()
' Driver Search
'Me.Filter = "[Driver In] Like " & Chr(34) & Me.Text9 & "" & Chr(34)
Me.Filter = "[Driver In] Like " & Chr(34) & Me.Text9 & "" & Chr(34) And [ApptDate] Like " & Chr(34) & Me.Text13 & Chr(34)"
Me.FilterOn = True
Me.Requery
End Sub

Private Sub Command60_Click()
' Appt Type Search
'Me.Filter = "[Appt TypeID] Like " & Chr(34) & Me.Text11 & "" & Chr(34)
Me.Filter = "[Appt TypeID] Like " & Chr(34) & Me.Text11 & "" & Chr(34) And "[ApptDate] Like " & Chr(34) & Me.Text13 & Chr(34)
Me.FilterOn = True
Me.Requery
End Sub


I have been using this forum to help solve my access problems and its been a great source of information.

Any help would be much appreciated I more information is need let me know

Regards

Andrew