Union for <All> (1 Viewer)

[Harris@Z]

Hi, I hope someone can assist

I want to include a "catch-all" in the recordsource for a combo box.
This works for an Access based table:
SELECT DISTINCT rBST.Received_Date FROM rBST UNION Select "All" From rBST
ORDER BY rBST.Received_Date DESC;

However, when creating the table in SQL, I am required to format the date, i.e.,
SELECT DISTINCT Format$([Received_Date],'yyyy-mm-dd') AS Expr1
FROM rBST
ORDER BY Format$([Received_Date],'yyyy-mm-dd') DESC;

The above works, but now when I add the Union for 'All', e.g.,
SELECT DISTINCT Format$([Received_Date],'yyyy-mm-dd') AS Expr1
FROM rBST UNION Select "All" From rBST
ORDER BY Format$([Received_Date],'yyyy-mm-dd') DESC;

Can anyone assist to solve this, or is this coding not possible?

Thanks

 

[arnelgp]

SELECT * FROM (SELECT Format$([Received_Date],'yyyy-mm-dd') AS Expr1
FROM rBST
GROUP BY Format$([Received_Date],'yyyy-mm-dd')
ORDER BY Format$([Received_Date],'yyyy-mm-dd') DESC
UNION Select TOP 1 "All" From rBST) ORDER BY IIF(Expr1="All", 0, 1)

 

[Harris@Z]

Wow, brilliant, thank you!
I would not have solved this ever!
Much appreciated.
Harris

 

[jdraw]

Here's a sample to include ALL and N/A in a Select query. If you have a leading space in " ALL", things will sort as expected.

SELECT format$(AnimalCapture.CaptureDate,'yyyy-mm-dd') as MyField
FROM AnimalCapture
union
select " ALL" from animalcapture
union
select " N/A" from animalcapture;

Sample result:
Query59 Query59

MyField
ALL
N/A
2008-02-04
2008-02-27
2008-12-03
2012-03-17
2012-05-29

 

[Harris@Z]

Thank you for this alternative, much appreciated!