combobox "all" for query criteria

I am using a combobox to select criteria for a query.
I have finally been able to add "all" to my picklist query
SELECT DISTINCT [Division group cross table].Division
FROM [Division group cross table] UNION select '(ALL)' from [division
group cross table]
ORDER BY [Division group cross table].Division;

However, if I select "all" then my query results do not include any
records. How do I change it so that if I select all, the query does
not limit the criteria to any of the organizations that are listed in
the dropdown?

TIA

For two columns, one for display in the combo box, the other a hidden column
(width 0") that will contain the value to be used by the query. This second,
hidden column will be the bound column, so it will be the Value of the combo
box after a selection. You would use Null as the value in the hidden column
when (All) shows in the visible column. In the query, you would then set the
criteria to

[Forms]![Form1]![Combo1] Or [Forms]![Form1]![Combo1] Is Null

This will return all values if the value of the combo box is Null (i.e. if
you select (All)) or the value selected if something else is selected.

For one column, assuming that (All) isn't among the possible selections,
modify the query criteria above to

[Forms]![Form1]![Combo1] Or [Forms]![Form1]![Combo1] = "(All)"

The first method would be used for combo boxes that display a user friendly
column and have an ID column for use by the database. The second method
should work with what you have.

--
Wayne Morgan
MS Access MVP


"papa jonah" wrote in message
ups.com...
I am using a combobox to select criteria for a query.
I have finally been able to add "all" to my picklist query
SELECT DISTINCT [Division group cross table].Division
FROM [Division group cross table] UNION select '(ALL)' from [division
group cross table]
ORDER BY [Division group cross table].Division;

However, if I select "all" then my query results do not include any
records. How do I change it so that if I select all, the query does
not limit the criteria to any of the organizations that are listed in
the dropdown?

TIA

My query only has one column.

Can I do this by somehow using code (from the command button on click
event) to provide the same results as having the event run a "query"?

Never mind, Wayne. It took me awhile, but I eventually figured out
where I was supposed to insert your suggestion. It looks like it works
great now.

Thank you very much!