Query criteria as a value or treat as nothing

matttan

New Member
Joined
Jan 13, 2005
Messages
39
Hi,

I have a query that I want to filter from 3 combo boxes on a form. My problem is that I want the filter to filter on the value in each box, or if th box is blank (ie, no selection made), to not filter at all. I am currently using the criteria section of the query.

To expand a bit further - there are 3 combo boxes. The filtering could be any combination of these - ie, up to 7 possible combinations of filter. Rather than having a separate query for each combination, I'd like to 'smart' the one query to do this properly.

Thanks for any help!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Gidday

In your query, put all 3 criteria onto the same row so that they all work together (i.e. all 3 must be satisfied like an AND operator).

I haven't tested this but change your criteria to this :

Code:
[Forms]![MyFormName].[MyComboBox1] Or Like [Forms]![MyFormName].[MyComboBox1] IsNull

Make sure you use your actual form name and combo box names.

HTH, Andrew :)
 
Upvote 0
Great - seems to work well. It did auto change my criteria to split it right out to 8 lines of criteria, but the results are the same...
 
Upvote 0
I'm pleased it worked and sometimes Access does that but it's no problem. 8 criteria sounds better than 7 given it is 2^3 combinations.

Cheers, Andrew
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top