Using multiple optional criteria.

[pharoah42]

I have a search form that has two combo boxes, each with a list of possible criteria to search for. For this example lets say I'm tracking purchases of one of three items in one of three states. I want my end user to be able to search for all purchases of item #1 in state #1 but also all purchases of item #1 in all states and all purchases of all items in state #1. Is there a way to design my query in such a way that leaving one of the boxes blank, or selecting "all" in that box, would allow the query to run with just the criteria from the other box?

[Seth Schrock]

There may be an easier way via SQL, but I use VBA to build a filter string based on the used search boxes.

[jforbes]

When using a Command Button or similar Form Event to create a Filter or the full SQL Select, I would agree with Seth, using VBA is the way to go as it is much more flexible. This is a pretty decent way to go about it: http://bytes.com/topic/access/insigh...filtering-form

Another option that appeals to some people is to create the Query in the Query Editor with the Where clause including references to TextBoxes or ComboBoxes on a Form. I'm not recommending this, but some people like it. This may not be the best Example, but it is dealing with Null values for a saved Query Definition: http://bytes.com/topic/access/answer...es#post3792346