Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010

    Unanswered: Search Form Mulitple Filters

    Hi, I have written some code to filter a table by a combo box and date fields. Whilst each code works fine independantly, when criteria is placed in all fields, it will not display the correct record and instead defaults to the date filter.

    Basically, I want them to work independantly if data is only entered in one or either criteria box, but also together.

    Can someone have a look at my code and point me in the right direction pls?

    Also, can anyone recommend a good book for developing skills in access / vba etc.. I dont want / need a beginners book, but I wouldn't mind something so I don't have to bother you guys asking so many questions..


    Private Sub SEARCH_Click()

    DoCmd.SetWarnings False

    Dim strWhere As String

    If Not IsNull(Me.CMDCLIENT) Then
    strWhere = strWhere & "[CLIENT] Like '*" & CMDCLIENT & "*'"
    End If

    If strWhere = "" Then
    MsgBox "ENTER INFORMATION", vbInformation, "Nothing to do."
    With Me
    .Filter = strWhere
    .FilterOn = True
    End With
    End If

    Dim sWhere As String

    If Not IsNull(Me.txtstartdate) Then
    sWhere = "ENTERDAT >= #" & Format(CDate(Me.txtstartdate), "mm/dd/yyyy") & _
    " 00:00# AND ENTERDAT <= #" & Format(CDate(Me.txtenddate), "mm/dd/yyyy") & " 23:59#"
    End If

    If sWhere = "" Then
    MsgBox "ENTER DATE RANGE", vbInformation, "Nothing to do."
    With Me
    .Filter = sWhere
    .FilterOn = True
    End With
    End If

    End Sub

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Take your coding mind off of the .Filter command. It will cause you nothing but problems. Instead, utilize setting the recordsource of the form (or subform) with criteria in the query. This will get you better results. See the code bank (page 6 or 7) on methods to 'filter' your subform based on criteria.

    There are also multiple examples in the code bank on how to setup criteria to filter records (without using the .Filter command.) - again, I'd avoid using the .Filter command!!
    Last edited by pkstormy; 03-01-10 at 22:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts