Do not require criteria input if user does not choose an option.

[USMCTL]

Cannot find another question like this, so If its been solved please send me link.



I have a form with 5 different list boxes. Manufacturer, Brand, Family, Flavor, and Product Item. I am making this form for distribution reports. But the reports are never the same so I want user to be able to chose criteria from either of the list boxes. Individual boxes work alone. But How can I use them all in regards to form but only make criteria if they choose something? I only want them to have to click the box if they want to change data. The rest of these boxes should remain in case they want to run something else. However I have failed to figure out how to make it so if they dont choose an option, it doesnt error the query. I want it to leave criteria blank as if nothing was changed if they do not change it. I tried default value blank string. Ive tried is null. Nz..anything I can think of but im just missing it. Please help.

[Micron]

Rather than reference form controls in your query (big assumption - you don't say) build the query sql statement in code by looping over the selections. Have a variable for the SELECT portion of the sql, and one for the WHERE part, and if need be, one for sort order. Concatenate the parts. If no criteria was selected, don't concatenate that variable as it will be a zero length string (""). Set the report recordsource to be that sql statement. Or use that sql statement and modify your stored query using query def sql property, then open the report based on the query itself.

[kd2017]

You want to build your filter (or where clause) in vba. If a textbox is left blank simply don't add that part to the filter.

Download the example here and study the code

http://allenbrowne.com/ser-62.html

[USMCTL]

I get this error what I do not choose from the list box. But If they dont want to change or choose criteria I dont want them to have to even mess with that field.

[Micron]

That is not necessarily an error. I could be an input prompt (aka parameter prompt). In your case I'd say it's not about not providing criteria, it's being raised because Access cannot figure out what you're referencing. Either the form or control is misspelled, or the syntax is incorrect when creating the reference (the hierarchy is wrong). Is your control on a subform? You don't get that message if you choose something from the listbox?

[USMCTL]

It is not an error, no. It is just asking me to input something. I used =[Forms]![MANUFACTURER]![lbEnterMfg] OR ... IS NULL. It worked but I still get the prompt. If I press OK, it correctly runs my query without choosing from list box and just using all records like i wanted it to. However, I am now trying to get it to not show that prompt. Thanks for the help.

[Micron]

3rd sentence, post 5 - likely your issue then; i.e. Access cannot figure out what [Forms]![MANUFACTURER]![lbEnterMfg] is. Same goes for the form control reference in your pic if that's the prompt you're now referring to. Perhaps misspelled, or the form containing that control isn't open? If you remain stuck, you could copy, compact and zip and post db here.