How do you run a query based on items selected on a form?

[bjayhome]

Hi all!

How do you run a query based on items selected on a form? I like to be able to have the option to select or leave blank (Text boxes, Combo boxes etc.) depending on the filtering I like to perform. I have used control names in criteria fields of my query. But if I leave any field on the form blank (null value), then I would not get any results. I need the flexibility to pick or not to pick items or leave them blank, but still get the results based ONLY on the items that have been selected on the form. I hope I am making myself clear. If not please let me know.

Thanks a bunch in advance.

BJ

 

[rac2]

I can't show you how to do this in Access but I can give you a concept that can be used for this problem.

Define an indicator variable that shows whether or not the item should be used in the filter. Then write the search condition for an item like this

WHERE (@anyColor OR color = @selectedColor)

Set @anyColor = true when color is not selected, set it to false when a value for color is selected.

The condition is true for all rows when @anyColor is true; it is true for rows with the selected color when @anyColor is false.

Use this pattern for each of the optional items.