Access dropdown filter add choice for "all"

For starters, here is how you get ALL - two examples:

SELECT DISTINCT CLng(EmpID), 2 FROM tblEmp
UNION SELECT "ALL", 1 FROM tblEMP
ORDER BY 2;


SELECT EmpName, 5 FROM tblEmp
UNION SELECT "<Add New>", 0 FROM tblEMP
UNION SELECT "-----------", 1 FROM tblEMP
UNION SELECT "<Select All>", 2 FROM tblEMP
UNION SELECT "-----------", 3 FROM tblEMP
ORDER BY 2;

Then, you need to handle the "ALL" case in the Combo box's AfterUpdate event ... to do what ever.

mx

I'm having a hard time understanding your code. Do I need to put both sections in?
Here is what I have so far:

SELECT DISTINCT CatWebWork2CopywritersQ.Username FROM CatWebWork2CopywritersQ ORDER BY CatWebWork2CopywritersQ.Username;

Select allOpen in new window

No ... the 2nd example was just an expanded example showing what 'can be done' - if you ever need more options.


SELECT DISTINCT CatWebWork2CopywritersQ.Username
FROM CatWebWork2CopywritersQ
UNION SELECT "<ALL>", "------------"
FROM CatWebWork2CopywritersQ
ORDER BY 2

Note: The orderby 2 means the 2nd column ... shortcut notation.

mx

Past the SQL into a UNION Query - in the query designer window ... then run it ...

The above is just to test the UNION query ... and confirm it shows the desired results ... 'ALL' s/b first in list.

mx

Hi!
I get an error:
"The number of columns in the two selected tables or queries of a union query do not match."

Great answer - thanks!

You are welcome ...

mx