I have the following SQL statement in code that is set to the RowSource of a
combobox. The combobox has two columns.
SELECT '(ALL)' As Site, '' As [Fac Count], 0 As SortFirst
FROM [BUILDING_TABLE]
GROUP BY SITE_COMPLEX
UNION SELECT SITE_COMPLEX As Site, Count(BLDG_ID) As [Fac Count], 1 As
SortFirst
FROM [BUILDING_TABLE]
WHERE Nz(SITE_COMPLEX,'') IN
(SELECT SITE FROM [USER_SITE_TABLE] WHERE ALLOWED_TO_VIEW = True And User_ID
= 'John_Doe') GROUP BY SITE_COMPLEX ORDER BY SortFirst, [Fac Count] DESC
I've split it up a bit, hopefully for easier reading. It works but I want
the sorting on Fac Count to be a numerical sort and not a textual sort.
Because I have in the first part of the statement (before the UNION), "" As
[Fac Count], the sorting is textual. If I change it to 0 As [Fac Count], the
sorting is numerical which is what I want. But I don't want 0 to show up
next to (All) in the combobox. I want it to be blank.
I tried
Null As Site - this gave gibberish for the counts in the combobox
Empty As Site - this caused an error.
As Site - this also cause an error
Don't know what else to try.
Thanks for any help.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200608/1