Query help...some criteria or none

[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!

 

[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

 

[Tara]

Thanks for the response. It worked perfectly!!

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

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!