ComboBox and a Parameter Query

[karmahum]

Hello. I am attempting to base a complicated query (which I do not create in SQL in code because of it's complexity) that has a number of parameters that are pulled from a form. The query works like a peach as long as a value is selected in combobox. The problem arises when the user does not want to query based on a particular field...They will indicate this by not selecting anything in that particular combobox. However, the query does not work under this situation.

I have seen an example of how to make this work, however, I can't seem to locate it right now.

So, basically, I am looking for help on how to feed the query (that is looking for a parameter ) the logic to return all values for that field (I know it invlolves the "*", however, it does not seem to work).

Any ideas would be greatly appreciated.

Thanks

 

[The_Doc_Man]

I believe that this is a "it depends" case.

If you are using the EQUALS operator (=) for all combo boxes, then this one is very easy.

Instead of using a query that includes

"... WHERE ... AND [FIELD7] = """ & [Parameter7] & """ AND ..."

make it

"... WHERE ... AND [FIELD7] LIKE """ & [Parameter7] & """ AND ..."

for ALL parameters. Then put a single asterisk (*) for any omitted parameter. This works because LIKE with no wild card characters is the same in effect as EQUALS. (With the possible exception of the way lower case characters are treated by LIKE.)

If there are any numerics in the query and they involve either the >= or <= cases, don't substitute an *. Instead, provide either the smallest or largest numeric value for such cases.

Where I used this, I added some pushbuttons to my form to select one of "EXACT", "BEGINS WITH", "ENDS WITH", and "CONTAINS" - which governed whether I tacked a leading, trailing, or leading AND trailing asterisk to whatever was entered. With the "LIKE" operator, that made it trivial to develop a pretty sophisticated query.

 

[karmahum]

Thanks for the response. The query is not being built via SQL in Code. Rather, I have created a query (which is the first in a series of queries used to get to the final product) using the Design View.

Does your method still work, since I am not creating the query in code?

 

[rpadams]

Would a QBF work?

Although I like to use the The_Doc_Man's "Like and wildcard" because of its flexibility and ease of use, if I have a number of different choices on whether or not certain criteria are used or not, a custom QBF makes it easier to predict the outcome. Try the link below. Article is for Access97, but links are in article for newer versions.

http://support.microsoft.com/default.aspx?scid=kb;en-us;95931