SELECT CustomerID, CompanyName FROM Customers UNION
Select Null as AllChoice , "(All)" as Bogus From
Customers ORDER BY CustomerID;
For the Union query to work you need to line up fields. The part in question
gives names to its fields of AllChoice and Bogus, thus the "As" statements.
When this is done, AllChoice will line up with CustomerID and Bogus will
line up with CompanyName. The ORDER BY clause in a Union query sorts based
on the field names in the first SELECT statement. Sorting Ascended, as in
this case, will sort Null values to the top. The CustomerID field would be
in the first column of the combo box and the CompanyName field in the second
column. The first column would be the Bound Column with a width of zero to
hide the ID, the CompanyName field is what the user would see with All at
the top since it lines up with the Null. It would probably be preferable to
sort on CompanyName, in which case the ( will put the All at the top as well
since it comes before "A".
?asc("(")
40
?asc("A")
65
?asc("a")
97
Selecting All will give the combo box a value of Null. When your query uses
this value in its criteria, it will return all records.
Sample criteria for doing this:
[Forms]![Form1]![Text0] Is Null Or =[Forms]![Form1]![Text0]
The reason this works, it the value in the table will be displayed if the
criteria is True. If the value returned by the combo box is Null then
"[Forms]![Form1]![Text0] Is Null" will be True. Since it will always be
True, regardless of the actual value in the table, then all values will be
returned. If you have a value in the textbox listed, then
"[Forms]![Form1]![Text0] Is Null" will return False, but the other side of
the Or will return True if the value in the table matches the value in the
textbox. As you know, if either side of an Or statement is True, then the Or
statement returns True.
