dynamic filters on query

[striker73]

I want to create a dynamic filter on a query I have. Is there a way to add a "where" statement to a query using VBA? I have a form and if the user selects "Show only zip code 92705" then I want to be able to add that condition to my sql statement. I tried just adding [Forms]![MyForm]![MyControl] to the criteria of the query, but sometimes the user won't need to sort by zip code, but by another field and if I leave this criteria in query, my query doesn't return anything. Any ideas? Thanks!!

 

[TTThio]

Hi,

how about setting the criteria to =
Forms![MyForm]![ctl1] or Forms![MyForm]![ctl2] etc, that way, as long as there's criteria selection, the query won't go blank.

Tin Tin

 

[TTThio]

I need to add something more.

If you'd like to return all records when the query is blank,
set like this:

ZipCode [EnterZipCode]
Is Null
[EnterZipCode] Is Not Null

Good Luck!