Filtering with multiple combos & toggling filters - VBA fault

[Pawtang]

I have the following form for searching records. It's designed to behave as you might expect: toggling check boxes turns that part of the filter on/off. I think I am close with the code I have here, but I am getting some unexpected behavior. When I run the search, it prompts me for the value of "BARRMEC" and then uses the value I type in as the filter for the customer field. Very strange, because it seems to be taking the value of the cbo box, assigning that as the name of a variable that is to be used in the filterText string, and then prompting me for it.

This seems like a classic comparison vs assignment fault but I can't figure out why its happening.

Option Compare Database

Private Sub btnSearch_Click()
Dim filterText As String


If Me.chkCust = True Then
    If filterText = "" Then
        filterText = filterText & "cust_nb= " & Me.cboCust
    Else
        filterText = filterText & " AND cust_nb= " & Me.cboCust
    End If
End If


If Me.chkPart = True Then
    If filterText = "" Then
        filterText = filterText & "partNumber= " & Me.cboPart
    Else
        filterText = filterText & " AND partNumber= " & Me.cboPart
    End If
End If


If Me.chkFail = True Then
    If filterText = "" Then
        filterText = filterText & "failureCategory= " & Me.cboFail
    Else
        filterText = filterText & " AND failureCategory= " & Me.cboFail
    End If
End If
 
If Me.chkDate = True Then
     If filterText = "" Then
         filterText = filterText & "Month(dateReceived)= " & Me.cboMonth & " AND Year(dateReceived)= " & Me.txtYear
     Else
         filterText = filterText & " AND Month(dateReceived)= " & Me.cboMonth & " AND Year(dateReceived)= " & Me.txtYear
    End If
End If
 
Me.Filter = filterText
Me.FilterOn = True
Me.Refresh
End Sub

[Welshgasman]

I'd do as Ranman256 showed you earlier.?
Start filter text as "1=1" then just concatenate to that.?

Your problem is you need to surround the values of your string fields with a single quote?, if it was dates it would be #. Numerics, you do not need anything.

Also helps if you start using Debug?
Debug.Print filtertext after each modification, then look in the immediate window to see what you actually get, not what you think you are getting?
That would have shown you this error.

[Pawtang]

I'd do as Ranman256 showed you earlier.?
Start filter text as "1=1" then just concatenate to that.?

Your problem is you need to surround the values of your string fields with a single quote?, if it was dates it would be #. Numerics, you do not need anything.

Also helps if you start using Debug?
Debug.Print filtertext after each modification, then look in the immediate window to see what you actually get, not what you think you are getting?
That would have shown you this error.

I didn't understand what the sWhere variable represented in Ranman's reply, or the purpose of 1=1. Is this basically a null state for the filter, allowing all of my strings to be concatenated so I don't need the nested conditionals? That would be nice.

Also, not sure if intentional or your computer/keyboard is doing something odd here, but you're adding question marks at the end of each of your sentences which makes it quite confusing to read seem to be doubting yourself!

[Welshgasman]

The ? are to indicate it is just a suggestion, not a statement.

Also in another thread, you said you used an ID field and a display field for your combos.?
Yet, if your filter is considering BARMEC as a variable, due to the incorrect syntax, that cannot be the case, surely? That ? is asking a question now.
If you had used an ID and display fields, then your syntax would have been correct as Me.cboCust would be numeric and then the filter would be of the correct syntax.

The sWhere is just a variable that holds the WHERE clause without the WHERE keyword. You just use FilterText instead.

[ssanfu]

Alternative code......

I don't know the field types in your table(s), but here is your code rewritten WITHOUT the check boxes.
This is based off of code provided by Allen Browne: Search Criteria

Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
    Dim filterText As String
    Dim lngLen As Long

    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboCust) Then
        filterText = filterText & "cust_nb= '" & Me.cboCust & "' AND "
    End If
    
    'Text field
    If Not IsNull(Me.cboPart) Then
        filterText = filterText & "partNumber= '" & Me.cboPart & "' AND "
    End If
    
    'Text field
    If Not IsNull(Me.cboFail) Then
        filterText = filterText & "failureCategory= '" & Me.cboFail & "' AND "
    End If
    
    'Text field
    If Not IsNull(Me.cboMonth) Then
        filterText = filterText & "Month(dateReceived)= '" & Me.cboMonth & "' AND "
    End If
    
    'Number field
    If Not IsNull(Me.txtYear) Then
        filterText = filterText & "Year(dateReceived)= " & Me.txtYear & " AND "
    End If
    
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(filterText) - 5
    
    If lngLen <= 0 Then                           'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                                          'Yep: there is something there, so remove the " AND " at the end.
        filterText = Left$(filterText, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print filterText
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = filterText
        Me.FilterOn = True
        Me.Refresh
    End If
End Sub

To remove filter

Private Sub btnRemoveFilter_Click()
    Me.cboCust = vbNullString
    Me.cboPart = vbNullString
    Me.cboFail = vbNullString
    Me.cboMonth = vbNullString
    Me.txtYear = vbNullString
    
    Me.Filter = vbNullString
    Me.FilterOn = False
End Sub