How to filter 3 text boxes and then run a report according to them in MS Access VBA

Question

I have 3 combo boxes:

1. Company - cboCOMP - tblCOMPANY
2. Category - cboCAT - tblCATEGORY
3. FLEET NO - cboFLT - tblFLEET_NO

These (1&2) are then sorted(criteria) via cboCOMP with the row source of tblFLEET_SETUP I have setup each combo box so that they filter into each other but have only managed to figure out how to do this according to the cboCOMP as long as it has a value selected.

Basically I want the combo boxes (1,2 & 3) to show their individual full list of options in the drop down even if the cboCOMP does not have a value selected but I then want the combo boxes to filter according to each individual combo box accordingly. This this possible and how would I do this?

Once I have selected the values I want to filter by I will click Run Report but everytime I do the only combo box that then gives me an error with regards to running the report is when I have selected a value for cboFLT. If I leave cboFLT blank but put a value in the other 2 combo boxes, the report runs fine. Here is the vba code I am using for this…

Private Sub btnRUN_Click()

Dim vcomp As String
Dim vcat As String
Dim vflt As String
Dim filter As String
        
    If Me.cboCOMP.Value <> "" Then
        vcomp = "'" & Me.cboCOMP.Value & "'"
        ' MsgBox vcomp
                
            filter = "COMPANY =" & vcomp & ""
            ' MsgBox filter
    End If
    
'NEW IF STATEMENT
            
    If Me.cboCAT.Value <> "" Then
        vcat = "'" & Me.cboCAT.Value & "'"
                
            If filter <> "" Then
                filter = filter & " and "
                        
                ' MsgBox filter
            End If
                    
        filter = filter & "CATEGORY =" & vcat & ""
        ' MsgBox filter
                
    End If
    
'NEW IF STATEMENT
             
    If Me.cboFLT.Value <> "" Then
        vflt = "'" & Me.cboFLT.Value & "'"
        
            If filter <> "" Then
                filter = filter & " and "
                
                ' MsgBox filter
            End If
            
        filter = filter & "FLEET NO =" & vflt & ""
        ' MsgBox filter
        
    End If
    
    DoCmd.OpenReport "rptQuick_Fuel_Report", acViewPreview, , filter
    DoCmd.Close acForm, "Quick Fuel Lookup", acSaveNo
    
End Sub

The error code is "

Run-time error '3705': Syntax error (missing operator) in query expression 'COMPANY = 'JB' and CATEGORY = 'SOAP' and FLEET NO = 'Q 16''.

When I click debug the error line in the code is:
DoCmd.OpenReport "rptQuick_Fuel_Report", acViewPreview, , filter

Answer 1

Field FLEET NO has a space. Object names with space or punctuation/special characters must be enclosed in [ ]: [FLEET NO]. Advise not to use those features in naming convention. – June7 yesterday