How do you run a query based on items selected on a form?

[bjayhome]

Hi all!

How do you run a query based on items selected on a form? I like to be able to have the option to select or leave blank (Text boxes, Combo boxes etc.) depending on the filtering I like to perform. I have used control names in criteria fields of my query. But if I leave any field on the form blank (null value), then I would not get any results. I need the flexibility to pick or not to pick items or leave them blank, but still get the results based ONLY on the items that have been selected on the form. I hope I am making myself clear. If not please let me know.

Thanks a bunch in advance.

BJ

 

[FoxProProgrammer]

One way to solve this issue is to build a Where clause on the fly in the On Exit routine of each control. If the control contains a null or empty value when the user exits the field, you wouldn't include it in the Where clause. When they execute the query (with a command button?), you would concatenate the final Where clause to the rest of the Query. dz
dzaccess@yahoo.com

 

[FancyPrairie]

Check out this thread (thread181-324343), although lengthy, shows how to create a Where clause based on the results of a multiple list box. And then how to create a query based on the results. The concept would be the same for what you're try to do (I think).

 

[JeremyNYC]

BJ,

I do it a more complex way, writing code to capture all the information I need. But there's a simple way built in to Access. If you open the form in question and click Records|Filter|Filter By Form you'll get a slightly altered version of your form that may do what you need.

Hope so.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[LetsGiveItAShot]

This isn't all that pretty, but it works and it's easy:
set the default values for the fields on your form to "*". That way, if they aren't entering data into the field, the query will return all results for that field. Then, in the where portions of your query, change the criteria to Like forms!form1!field1 .. This works in numeric fields as well as text fields.

Hope it helped.

 

[billcarter]

There is a free program, Ffill, which does what you are attempting to do. I use it in all my programs.
When I figure out how to attach it I'll send it along.