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!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :)
 

matttan

New Member
Joined
Jan 13, 2005
Messages
39
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...
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,640
Members
412,334
Latest member
ExcelForLifeDontHate
Top