Solved Combining number and text filters (1 Viewer)

[Antti]

Hi. I'm having problems with filtering a form. What I'm trying to achieve is having two combo boxes, one with number value (Employee ID) and another with text value (Item code). User should be able to filter both simultaneously, so that it would be possible to show records of a certain item taken/added by certain employee. I have a similar thing in another form with two text fields, but now that the data types are different, I'm lost.

Here is a code I'm currently working with, this would be called from both combo boxes' AfterUpdate event. Now I get a type mismatch (error 13).

Private Sub actionFilter()
    Dim actionFilter As String

    If Not IsNull([ItemCombo]) Then
        actionFilter = actionFilter & "[ItmBarcode]= """ & Me.ItemCombo.Column(0) & """" And ""
    End If
    actionFilter = actionFilter & "[ACTION_T].[EmployeeID]=" & Me.EmployeeCombo.Column(0)
    Me.Filter = actionFilter
    Me.FilterOn = True
End Sub

Any help or pointing to the right direction would be appreciated.

 

[bob fitz]

Hi. I'm having problems with filtering a form. What I'm trying to achieve is having two combo boxes, one with number value (Employee ID) and another with text value (Item code). User should be able to filter both simultaneously, so that it would be possible to show records of a certain item taken/added by certain employee. I have a similar thing in another form with two text fields, but now that the data types are different, I'm lost.

Here is a code I'm currently working with, this would be called from both combo boxes' AfterUpdate event. Now I get a type mismatch (error 13).

Private Sub actionFilter()
    Dim actionFilter As String

    If Not IsNull([ItemCombo]) Then
        actionFilter = actionFilter & "[ItmBarcode]= """ & Me.ItemCombo.Column(0) & """" And ""
    End If
    actionFilter = actionFilter & "[ACTION_T].[EmployeeID]=" & Me.EmployeeCombo.Column(0)
    Me.Filter = actionFilter
    Me.FilterOn = True
End Sub

Any help or pointing to the right direction would be appreciated.

Is the Employee field "[ACTION_T].[EmployeeID]". Perhaps you just need "[EmployeeID]"

 

[Gasman]

Debug.Print ActionFilter and see what you actually have, instead of guessing.
Numbers do not need quotes, strings do, dates need #

 

[CJ_London]

You don’t need the .column(0) if this is the bound column

And think you only need one double quote after the AND

suggest you use single quotes rather than the double double quotes as easier to read

also which line is giving you the error?

finally use debug.print actionfilter to see what your string looks like

 

[Antti]

I just noticed that I had 4 instead of 3 double quotes in one place and that's why that line gave me an error. Now I know what to fix to get this working. Thanks for the answers, they sure helped me.