Criteria problems

[Guest]

I am trying to create a query based on data entered in a form (the data comes
from drop down lists from multiple tables). Everything is going smoothly,
except for a couple of criteria problems. Some of the form fields may be
left blank while others are filled in. So if in the criteria row under a
particular characteristic I enter <Like [Forms]![frmFieldMatch]![Combo24]>
and under the next characteristic I enter <Like
[Forms]![frmFieldMatch]![Combo34]>, I only get records back where both
critera are true, of course. That's not what I want. My next idea was to
move all my criteria down to the "Or" row. That seemed to work for a little
while, until I reopened the query and they had moved back up to the
"Criteria" row. How do I get around that? Here's a description of the final
results that I'm looking for: I want records returned that have the selected
characteristics entered on the form, but where the form is blank, all's fair.


For example, if I select "elephant" under the "Animal" list and "purple"
under the "color" list, but I do not select a choice under the "size" list, I
should come up with all purple elephants of ANY size, not all purple
elephants with a blank "size" field.

I really hope this makes some kind of sense; let me know if you need more
info.

 

[Guest]

You need to construct your SQL query in code, based on the contents of fields
of the form. If a field has an entry, you and a criterion to the WHERE clause
of your query, where it does not, no criterion. If no fields have entries,
don't add a WHERE clause.

To follow your example:
SELECT list of columns FROM list of tables
WHERE species="elephant"
AND color = "purple"

If the size field has an entry, you would append to the query:
AND size="huge"

 

[Guest]

That sounds like it would work for retrieving data once, but when you change
the data on the form, you'll have to write a new query, right? I want users
to be able to select from about 10 dropdown lists, each with a different set
of characteristics. I have to structure the query so that the results match
whatever is in the boxes.

The point of all this is to generate reports over and over based on the
information selected in the boxes of the form. Am I on the entirely wrong
track?



Jolene Updike
Virginia Department of Historic Resources

You need to construct your SQL query in code, based on the contents of fields
of the form. If a field has an entry, you and a criterion to the WHERE clause
of your query, where it does not, no criterion. If no fields have entries,
don't add a WHERE clause.

To follow your example:
SELECT list of columns FROM list of tables
WHERE species="elephant"
AND color = "purple"

If the size field has an entry, you would append to the query:
AND size="huge"

--
Ted

I am trying to create a query based on data entered in a form (the data comes
from drop down lists from multiple tables). Everything is going smoothly,
except for a couple of criteria problems. Some of the form fields may be
left blank while others are filled in. So if in the criteria row under a
particular characteristic I enter <Like [Forms]![frmFieldMatch]![Combo24]>
and under the next characteristic I enter <Like
[Forms]![frmFieldMatch]![Combo34]>, I only get records back where both
critera are true, of course. That's not what I want. My next idea was to
move all my criteria down to the "Or" row. That seemed to work for a little
while, until I reopened the query and they had moved back up to the
"Criteria" row. How do I get around that? Here's a description of the final
results that I'm looking for: I want records returned that have the selected
characteristics entered on the form, but where the form is blank, all's fair.


For example, if I select "elephant" under the "Animal" list and "purple"
under the "color" list, but I do not select a choice under the "size" list, I
should come up with all purple elephants of ANY size, not all purple
elephants with a blank "size" field.

I really hope this makes some kind of sense; let me know if you need more
info.

 

[Marshall Barton]

Ted's approach works fine and, Yes, you need to construct
the SQL statement ech time the serach criteria is changed.
This is the most flexible way to do what you want, but if
you are not comfortable creating a nontrivial amount of VBA
code, there is another way.

Set the query's criteria to:

Like Forms!frmFieldMatch!Combo24 OR
Forms!frmFieldMatch!Combo24 Is Null

all on the Criteria line.
--
Marsh
MVP [MS Access]

That sounds like it would work for retrieving data once, but when you change
the data on the form, you'll have to write a new query, right? I want users
to be able to select from about 10 dropdown lists, each with a different set
of characteristics. I have to structure the query so that the results match
whatever is in the boxes.

The point of all this is to generate reports over and over based on the
information selected in the boxes of the form. Am I on the entirely wrong
track?

You need to construct your SQL query in code, based on the contents of fields
of the form. If a field has an entry, you and a criterion to the WHERE clause
of your query, where it does not, no criterion. If no fields have entries,
don't add a WHERE clause.

To follow your example:
SELECT list of columns FROM list of tables
WHERE species="elephant"
AND color = "purple"

If the size field has an entry, you would append to the query:
AND size="huge"

I am trying to create a query based on data entered in a form (the data comes
from drop down lists from multiple tables). Everything is going smoothly,
except for a couple of criteria problems. Some of the form fields may be
left blank while others are filled in. So if in the criteria row under a
particular characteristic I enter <Like [Forms]![frmFieldMatch]![Combo24]>
and under the next characteristic I enter <Like
[Forms]![frmFieldMatch]![Combo34]>, I only get records back where both
critera are true, of course. That's not what I want. My next idea was to
move all my criteria down to the "Or" row. That seemed to work for a little
while, until I reopened the query and they had moved back up to the
"Criteria" row. How do I get around that? Here's a description of the final
results that I'm looking for: I want records returned that have the selected
characteristics entered on the form, but where the form is blank, all's fair.


For example, if I select "elephant" under the "Animal" list and "purple"
under the "color" list, but I do not select a choice under the "size" list, I
should come up with all purple elephants of ANY size, not all purple
elephants with a blank "size" field.

 

[Guest]

You do not write the query, you write VBA code that tests the values of
fields on a form and constructs a string containing the Select statement with
a criterion for every field which has a non-null entry, then executes the
query. The only way to accomplish what you want is with VBA programming.