Errors with custom filter

[StephenH]

Hello all

I'm having a rather large headache with a custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.

Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.

The code I have at the moment is as follows:

'------------------------------------------------------------
' cmdApplyFilter_Click
'
'------------------------------------------------------------
Private Sub cmdApplyFilter_Click()

Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & "" And "[Location] = " & Me.cmbFilter4 & ""
Me.FilterOn = True

End Sub​

The current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.

 

[DavidAtWork]

The error is probably due to incorrect syntax around your filter objects, you need to taylor the syntax according to data types for each filter, using your example:

Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & "" And "[Location] = '" & Me.cmbFilter4 & "'"

This would be suitable when [Asset Group} is a number and [Location] is a string


David

 

[namliam]

The error is probably due to incorrect syntax around your filter objects, you need to taylor the syntax according to data types for each filter, using your example:

Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & " And [Location] = '" & Me.cmbFilter4 & "'"

This would be suitable when [Asset Group} is a number and [Location] is a string


David

I removed 2 quotes that will (also) be causing an issue

 

[StephenH]

@David: Both data types are string

@namliam: I've removed the extra quotation marks and it works, somewhat. Now it's asking for parameter boxes for both filter options, which it hasn't done before.

 

[pr2-eugin]

Sneaky me..

Me.Filter = "[Asset Group] = '" & Me.cmbFilter1 & "' And [Location] = '" & Me.cmbFilter4 & "'"

 

[StephenH]

@pr2-eugin: That worked nicely, thanks.

After some fiddling, I've managed to include the other three combo boxes as follows:

Me.Filter = "[Asset Group] = '" & Me.cmbFilter1 & "' And [Calibrated?] = '" & Me.cmbFilter2 & "' And [PAT Tested?] = '" & Me.cmbFilter3 & "' And [Location] = '" & Me.cmbFilter4 & "' And [User] = '" & Me.cmbFilter5 & "'"​

Except now it doesn't want to filter by one selection any more - it asks for each filter if there's no option selected. Any advice would be nice.

 

[pr2-eugin]

You need to build the filter string..

Dim filterStr As String

If Me.cmbFilter1.ListIndex <> -1 Then filterStr = filterStr & "[Asset Group] = '" & Me.cmbFilter1 & "' And "
If Me.cmbFilter2.ListIndex <> -1 Then filterStr = filterStr & "[Calibrated?] = '" & Me.cmbFilter2 & "' And "
If Me.cmbFilter3.ListIndex <> -1 Then filterStr = filterStr & "[PAT Tested?] = '" & Me.cmbFilter3 & "' And "
If Me.cmbFilter4.ListIndex <> -1 Then filterStr = filterStr & "[Location] = '" & Me.cmbFilter4 & "' And "
If Me.cmbFilter5.ListIndex <> -1 Then filterStr = filterStr & "[User] = '" & Me.cmbFilter5 & "' And "

If Len(filterStr) > 0 Then
    filterStr = Left(filterStr, Len(filterStr)-4)
    Me.Filter = filterStr
    Me.FilterOn = True
End If

 

[StephenH]

@pr2-eugin: Thanks Paul, that works perfectly. Much appreciated

 

[pr2-eugin]

Glad to help StephenH. Good luck.

 

[namliam]

and erm ffs.... use a proper naming convention for your combo's
Instead of cmbFilter5, cmbUser or something? Makes for much easier maintenance.