query criteria

[Terri]

Is it possible to input data onto a form and have that
information transfer to the design view criteria line of
another query ? I have a complex query that draws from 6
tables and the criteria in each field is changed every
time it is run. The query is used to retrieve info from
the tables but the table data must remain constant. I am
trying to find a faster way to input the main query
criteria. Any ideas would be much appreciated. Terri

 

[John Vinson]

Is it possible to input data onto a form and have that
information transfer to the design view criteria line of
another query ? I have a complex query that draws from 6
tables and the criteria in each field is changed every
time it is run. The query is used to retrieve info from
the tables but the table data must remain constant. I am
trying to find a faster way to input the main query
criteria. Any ideas would be much appreciated. Terri

You can use forms references as parameter queries. Type

=[Forms]![NameOfYourForm]![NameOfTheTextbox]

on the Criteria line in your query. If the form is open, it will use
whatever value is in that textbox as a criterion.

 

[John Vinson]

What if there are multiple text boxes on the form but
not all of them may be used each time the report is run.
For example I have six possible criteria but I may only
want to choose three and leave the rest blank. How do
you tell the query to ignore the text boxes that have
been left blank?

Two ways:

- Use a criterion like

=Forms!Formname!controlname OR Forms!Formname!controlname IS NULL

This makes the query pretty complicated if you have many fields - six
would be about the limit for me.

- Write VBA code to poll through all the controls on the form building
a SQL string as you go, including only those controls with values. See
http://www.mvps.org/access and search for "Query By Form".