ALL as choice in Combo Box.

Topic

New Reply

I have set up a statement:

SELECT DISTINCT [Locations].[Region] FROM Locations UNION select null as AllChoice, “(ALL)” as bogus from locations ORDER BY region;

in an attempt to add the choice of “ALL” in a combo that generates a report for me. I am getting an error:

‘The number of columns in the two selected tables do not match’

Any help?

Reply | Quote

Replies

I believe you need to select 2 columns from your Location table in order to form the Union. Also, it is not clear to me why you Select Null. Nonetheless, here is the code I use for an All Regions choice in a combo box:

SELECT DISTINCTROW Region.[Region Code], Region.[Region Name] FROM Region UNION SELECT “*”, “All Regions” FROM Region;

HTH

Reply | Quote

The situation is there are 6 regions and 233 store names. If I select more than one field from the locations table the SELECT DISTINCT displays too many choices.

The selection in this combo becomes the criteria in the region line of the query. If no distinct region is selected the criteria should be null.

The “ALL” selection in your suggestion does not yield the correct result… the report is blank as it does not recognize that ALL selected should be a null in the query criteria line.

I am still trying…

Reply | Quote

Instead of trying to make the criteria line Null (I assume you mean blank instead of Is Null, right?), use the approach previously suggested but put a Like operator in your criteria line. The expression “Like ‘*'” (without the outter quotes) returns any value for that field, including nulls.

Reply | Quote

Please forgive my beginnerness… where would you put the LIKE statement in the expression above?

I am learning that usign Access at this level is like trying to learn another language. “I am in China trying to order a pizza and everyone is showing me where the bathroom is”… I will get there…

Reply | Quote

I didn’t phrase it well. What I meant was that the selection of the All item in the combobox should return an asterisk. Then in the recordsource of the report, use the like operator. I would set the combobox source to something like this:[indent]

---

SELECT DISTINCTROW Region.[Region Code], 2 AS SortKey FROM Region UNION SELECT “*” As [Region Code], 1 As SortKey FROM Region OrderBy SortKey, [Region Code]

---

[/indent]Then, if you base the report on the combobox contents, the SQL should read something like this (I assumed you were using a table named Stores and that your selection from was called MyForm):[indent]

---

SELECT * FROM Stores WHERE Stores.[Region Code] Like Forms!MyForm!cboRegion

---

[/indent]

Reply | Quote