Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Unanswered: filtering using multiple comboboxes...

    i have a form with 7 combo boxes that i want to use for filtering...

    Me.Form.Filter = "[company]= " & Me.Combo24 & " And [category]= " &_ Me.Combo101 & " And [status]= " & Me.Combo105 & " And [reportedby]= " &_ Me.Combo37 & " And [assignedto]= " & Me.Combo107 & " And [urgency]= '" &_ Me.Combo109 & "' And [priority]= '" & Me.Combo26 & "'"

    Me.Form.FilterOn = True

    the code works fine, my problem is if one combo box is empty the form does not return any record. how can i handle blank criterias?

    any reply will be very much appreciated!

  2. #2
    Join Date
    Sep 2006
    Posts
    265
    Try:

    Code:
    Me.Form.Fliter = "[company] like '" & Me.Combo24 & "*" & "' AND [category] like '" & Me.Combo101 & "*" & "' AND [status] like '" & Me.Combo105 & "*" & "' AND [reportedby] like '" & Me.Combo37 & "*" & "' AND [assignedto] like " & Me.Combo107 & "*" & "' AND [urgency] like '" & Me.Combo109 & "*" & "' AND
    [priority] Like '" & Me.Combo26 & "*" & "'"
    Forgive me if the syntax is not perfect, but the idea is that it does not matter whether or not any of the fields are entered by the user, the search will be performed looking for all records and by completing each of the fields the search criteria is narrowed down.

    Simon

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    build your sql for the filter

    dim strSQL as string
    strSQL = "1=1" ' a magic number meaning that the sql will always be valid
    if Me.Combo24.selected = true then strsql = strsql & " and [company]= " & Me.Combo24

    'not to sure is its .selected property, you may have to test if the dombo has a value....
    then
    me.form.filter = strsql
    me.form.filteron=true
    .. and so on
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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