Optional Parameters

[Dave H]

I would like to build a query where it retrieves data from 4 seperate comboboxes. However I want to give the user the option of leaving the box blank and treating a blank as a wildcard. How do I setup the query to treat a blank as a wildcard?

 

[the_net_2.0]

you cannot use a cbox as a wildcard, Dave, but a blank can give you, for example, 'ALL' the options available. for instance, this:

where field1 = forms!form!cbox or forms!form!cbox is null

will either give you one of two results:

• records where the field = the cbox value, OR:
• all records, if the cbox is null, or blank

in other words, a form reference that is null, and has that syntax in a query statement, will result in that particular portion of the WHERE clause being completely ignored (hence, all records will be returned if there is only one conditional, like above). If, say, you have 3 conditions in the WHERE clause, and one of the references is NULL, in actually, the query will only evaluate the other two conditions, and ignore the one that referenced a NULL on the form.

you can do this as many times as you wish in a query, I believe, until the syntax becomes too complex for the engine to interpret, that is.

as a side note, which I just learned myself not too long ago, the logic of it really makes sense, because if the engine ends up evaluating:

forms!form!cbox is null

obviously the answer will always be TRUE, thus every record has to be returned. I mess around with PHP a bit, and the same thing is true for SQL injection, where you can insert things like "?1=1" into a URL and get otherwise sensitive information, if it's not prevented by the script.

 

[Brianwarnock]

This covers it from the design grid view if you prefer that.

Brian