Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filters on Multiple Combo Boxes (2003)

    How do I filter records with multiple combo boxes on a Form, so that when selections are made from say more than 1 combo boxes at the same time, they work together like AND statements, but if they are left blank then ALL records are returned, so that the Query is filtered progressively by each combo box selection, and not filtered if no selection is made?
    Thanks
    Allison

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filters on Multiple Combo Boxes (2003)

    You can use the following as starting point. You'll have to adapt it for your situation, of course
    <code>
    Dim strWhere As String

    ' Check first combo box
    If Not IsNull(Me.ComboBox1) Then
    strWhere = strWhere & " AND Field1=" & Me.ComboBox1
    End If

    ' Check second combo box
    If Not IsNull(Me.ComboBox2) Then
    strWhere = strWhere & " AND Field2=" & Chr(34) & Me.ComboBox2 & Chr(34)
    End If

    ' Check third combo box
    If Not IsNull(Me.ComboBox3) Then
    strWhere = strWhere & " AND Field3=#" & Me.ComboBox3, "mm/dd/yyyy") & "#"
    End If

    If strWhere = "" Then
    Me.Filter = ""
    Me.FilterOn = False
    Else
    ' Get rid of first " AND "
    strWhere = Mid(strWhere, 6)
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    </code>
    In the above example, Field1 is a number or Yes/No field, Field2 is a text field and Field3 is a date field.
    The example can easily be modified for different field types and expanded to accomodate more conditions.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filters on Multiple Combo Boxes (2003)

    Thanks you very much
    Allison

Posting Permissions

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