Variables in a query

[pyromaniac]

I have a

 UNION SELECT 0, ' All'  FROM tblBrands

so that I get an all option in my dropdown. I also have a

[Forms]![frmMain]![cboBrands]

in my query criteria to filter results from the combo box. When all is selected, I get no results. I'm thinking I need an if statement. if all is selected, run this query without the criteria else run query with criteria.

Is there a way to do this in either SQL, macro or VBA?

 

[jdraw]

There is an example here.

 

[pyromaniac]

I have gotten the dropdowns to change properly, Where I am struggling is when the query reaches back to look at the current state of the combo box, it doesn't seem to like the all option.

 

[Gasman]

The link given uses the AfterUpdate event to create the sql for the listbox.
If you are using a form control as the parameter in a saved query, I suggest you could use a hidden control that you set depending on the value of the combo?

* will get all rows in the query.

Then use that in your query.

HTH