Multiple Combo Boxes in a Single Query Criteria

[GendoPose]

Hi all!

Moving on from my last question, I managed to get seperate combo boxes to filter out results on a query, however now I have a slightly different problem.

I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.

Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.

The code I'm using in each criteria box is;

Like "*" & [Forms]![MyForm]![Combo1] & "*"

and this works as a single criteria in a single field.

Any help would be greatly appreciated!

 

[David R]

Stupid question, but are you using OR between your criteria? AND won't work for obvious reasons...

 

[GendoPose]

Yeah I've tried using both AND and OR, still with no luck. I had a go at making a Union query as well and all I kept getting was an SQL error saying it expect DELETE, SELECT and some others.

 

[David R]

You're now into a thing called 'filter by form' which is useful but a little more complicated.

Essentially what you're going to do is remove the criteria from the query itself and add it as a FILTER to your form. This should happen via VBA, because you need to check if one, the other, or both criteria are applied.

Essentially your Click action for the filter button (or AfterUpdate for each criteria field, but you'll have to duplicate code that way) will look something like this:

Private Sub Command34_Click()
Dim strSearch
   'does the first combo have anything in it?
   IF(Me.comboFirst <> "") Then strSearch = "[Name] Like " & Chr(34) & Me.comboFirst & "*" & Chr(34) 
   'does the second combo have anything in it?
   IF(Me.comboSecond <> "") Then 
      IF(strSearch <> "") Then 'concatenate the search string from both combos
         strSearch = strSearch & " AND "[Name] Like " & Chr(34) & Me.comboSecond & "*" & Chr(34) 
      Else 'just the second combo
         strSearch = "[Name] Like " & Chr(34) & Me.comboSecond & "*" & Chr(34) 
      End If
   End If

   if strSearch = "" then Exit Sub 'no criteria applied, no reason to filter
   Me.Filter = strSearch
   Me.FilterOn = True
End Sub

I strongly recommend renaming your fields to something more memorable - your debugging job will become 73x easier.

 

[GendoPose]

Thanks for that, I'll give it a go on Monday! My combo boxes are named appropriately, I just put down Combo1, etc as an example for you. It looks a little overly complicated, when all I really want to do is filter several envelope sizes across different production departments so I can ultimately produce a report for my line manager. If this doesn't work, I'll probably just export seperate query's to excel, import it to a table in access and make a pretty report that way. Thank you for the help of course though!

Also which parts of that code should I substitute? Obviously Command34 is the button and Combo1 is whatever combo I choose, are there any others?

 

[David R]

It's not really that complex if you look at what the different IF statements are doing... have you ever made a flowchart? This is the same thing in VBA; you need to account for all possible options the filter could encounter, otherwise someday 7 months from now your boss will click something STUPID and get bad data back. Computers are so literal!