Query Problem

[mimottershaw]

Hi All,

I have a query set up to use the input from a combo box in a form as my criteria. This works fine if I enter data in it, but I also want the query to include ALL records if the combo box is left blank. Basically I have multiple filtering criteria, and I want the Query to ignore all the combo boxes that I don't enter filter data into.

I'm sure this is a common requirement, but I don't seem to be able to find the right function or syntax to make it work. I've tried the IIF function in various forms with no success. Can anybody help please.

Many thanks,

Mark.

 

[ajetrumpet]

http://www.access-programmers.co.uk/forums/showthread.php?p=905623#post905623

congrats. a new FAQ has been created largely due to the occurance of this question.

 

[mimottershaw]

Hi Adam,

Thanks very much for your assistance. I'm relieved I'm not the only one to have got stuck with this issue. Unfortunately my knowledge of Access is pretty much limited to built in functions and applications, and I really start to struggle with SQL queries. My Query is several fields taken from a table, and I'm using a built in query. Do you know of a way, function, command or syntax that I can enter in each criteria field to either return specific records, or all records if each field is empty? I enter all of my criteria on a form at once rather than step by step as in your example, and I'm afraid I'm not up to the job converting it.

I'm sorry I am unable to understand you advice, and I'd be really grateful if you could help me with a solution that can be entered in a criteria box of several fields in a built-in query.

Thanks again,

Mark.

 

[ajetrumpet]

mark,

the faq is the same way you do it in YOUR query. if you seriously cannot figure it out, upload the database to this thread and I will do it for you.

 

[mimottershaw]

Sorry I can't. Could I send it to you as a private message as it isn't ideal to post it on an open forum, even though there is no genuine data?

Thanks,

Mark

 

[ajetrumpet]

Sorry I can't. Could I send it to you as a private message as it isn't ideal to post it on an open forum, even though there is no genuine data?

Thanks,

Mark

sorry mark. we should really keep it here so everyone can benefit from the outcome.

 

[John Big Booty]

There's also a sample and explanatory link here