Access Dynamic Query

[sccses]

I am using a Parameter Select based on the value in a combo box on a form. The form has multiple combo boxes and the purpose is to go in and be able to perform a dynamic query based on multiple fields and the values I select for the fields. If I dont select a value in that field it should not limit the returned value. IF the value in the form is null it returns no records. Is there a way to set it up to return all the records if blank. I have tried using Like ([Forms]![Form]![Field]) & "*" in the criteria and it returns all of the possible values except null values. I also tried puting or Is Null and it gave me all the records everytime. And IS Null gave me no records. Basically I want it to search on value I give it in the form but if the form has no value I want it to give me all the records. Thanks

 

[PHV]

And this ?
Like [Forms]![Form]![Field] OR [Forms]![Form]![Field] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[grnzbra]

What are you doing with the output of the query? If you are driving a report, perhaps you could loop through the combo boxes and build a WHERE clause for a filter. If there is an entry, add it to the WHERE claus, and if there isn't, dont. Then use the where clause as a filter.

I expect that you could do the same thing and modify the query to add the where clause to the query's SQL statement. I think it would involve creating and deleting the query whenever you ran it.

 

[sccses]

[Forms]![Form]![Field] OR [Forms]![Form]![Field] Is Null works for one combo box and will return all the records if no value is in combo box. When I add it to another combo box it does not return any values.

 

[FancyPrairie]

Check out this FAQ faq181-5497 It contains a function that will build the Where clause for you. It doesn't care how many combo boxes you have on your form. And it works for single and multi-select list boxes, text boxes, date ranges, option groups and check boxes. The key to making it work is defining the Tag property of the combo box correctly. This is explained in the FAQ and in the header of the function.

 

[grnzbra]

FancyPrarie,

I checked that FAQ and it contains an interresting process of building up the WHERE claus, doesn't seem to put it into a query. Is there any way to get it into a query without deleting the query and then creating a new one?