"all" in dropdown not working for query

[papa jonah]

I have a dropdown that is populated by the following query - but it
doesn't do what I want.

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;

I want the "all" part of it to remove the restrictive criteria. I have
seen some postings that tell you to use another column that is
invisible to the user. How do I add this other column to this query?

 

[fredg]

I have a dropdown that is populated by the following query - but it
doesn't do what I want.

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;

I want the "all" part of it to remove the restrictive criteria. I have
seen some postings that tell you to use another column that is
invisible to the user. How do I add this other column to this query?

You're fine so far, but you have to tell the query what to do with the
value.

Set the criteria for the Division field to:

Like IIf(forms!FormName!ComboName = "All","*",
forms!FormName!ComboName)

 

[papa jonah]

Fred,
I have included this as my criteria for the division field of my query.
Like IIf([Forms]![query form]![cbodivision]="(All)","*",[Forms]![query
form]![cbodivision])
But I am not getting anywhere.
I use cbodivision as an input to multiple queries depending on the
button that is clicked. One button works fine when I select (All). The
other does not. When I tried to make the query for the one that does
not work match the structure of the one that does, I get an error in my
docmd line.

For the query that works
I created a new field: [Forms]![query form]![cbodivision] with "(All)"
as the criteria - with all of the other criteria blank. The other
option within this criteria has
[Forms]![query form]![cbodivision] as the criteria for the "Division"
field.

This same approach is not working in the other query - which is much
more complex.