Query help...some criteria or none

T

Tara

I have a query that I'd like to filter with criteria in 2 different fields.
However, I'd like to give the user the choice of filtering by either one
field only or both fields. So for example, I'd like to allow the user to
choose to filter by either all John Smith's or all Smith's regardless of
first name. How would I set the query up for this?

Thanks!
 
V

vanderghast

Leave the unfiletering criteria as a NULL, and use the criteria:

WHERE ((paramField1 IS NULL) OR field1 = [paramField1] )
AND ((paramField2 IS NULL) OR field2 = [paramField2] )


or some variation on that theme.

Vanderghast, Access MVP
 
T

Tara

Thanks for the response. It worked perfectly!!

vanderghast said:
Leave the unfiletering criteria as a NULL, and use the criteria:

WHERE ((paramField1 IS NULL) OR field1 = [paramField1] )
AND ((paramField2 IS NULL) OR field2 = [paramField2] )


or some variation on that theme.

Vanderghast, Access MVP


Tara said:
I have a query that I'd like to filter with criteria in 2 different fields.
However, I'd like to give the user the choice of filtering by either one
field only or both fields. So for example, I'd like to allow the user to
choose to filter by either all John Smith's or all Smith's regardless of
first name. How would I set the query up for this?

Thanks!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with query results 8
Selecting and counting records in one query 3
Query Criteria Not Working 2
VBA: How can I use a string variable as autofilter criteria? 4
use field list from form to specify query fields 2
Changing query criteria to supply a form 3
Multiple Parameter Crosstab query filter by form 1
Query using multiple values from one text box 7

Top