Multiple Criteria Search Filter on Query (1 Viewer)

Private Sub cmdFilter_Click()
 'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************


    If Not IsNull(Me.ComboCompany) Then
        strWhere = strWhere & "([COMPANYID] = " & Me.ComboCompany & ") AND "
    End If
    
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.ComboSupplier) Then
        strWhere = strWhere & "([aglgrnSUPPLIER] = """ & Me.ComboSupplier & """) AND "
    End If
    
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.ComboProduct) Then
        strWhere = strWhere & "([ADVISEDPRODUCT] = """ & Me.ComboProduct & """) AND "
    End If
    
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.TextDeliveryDateSearch) Then
        strWhere = strWhere & "([SCANINTIME] >= " & Format(Me.TextDeliveryDateSearch, conJetDate) & ") AND "
    End If
    
    If Not IsNull(Me.TextDeliveryDateSearchToo) Then   'Less than the next day.
        strWhere = strWhere & "([SCANINTIME2] <= " & Format(Me.TextDeliveryDateSearchToo, conJetDate) & ") AND "
    End If

    If (Me.FrameDeliveryPreAdvice = 3) Then
        strWhere = strWhere
    End If
        
    If (Me.FrameDeliveryPreAdvice = 2) Then
    
    strWhere = strWhere & "([DeliveryType] = 2) AND "
    End If
    
    If (Me.FrameDeliveryPreAdvice = 1) Then
    
    strWhere = strWhere & "([DeliveryType] = 1) 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(strWhere) - 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.
        strWhere = left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.subfrmWarehouse.Form.Filter = strWhere
        Me.subfrmWarehouse.Form.FilterOn = True
    End If
End Sub

Format([B]CDate(Me.TextDeliveryDateSearch)[/B], conJetDate)