Sort Date in SQL UNION statement

[KWarzala]

Hello,



I have a combo box with the following Row Source:

SELECT DISTINCTROW tblReturnMerchandise.ReturnDate
FROM tblReturnMerchandise
UNION
SELECT "" FROM tblReturnMerchandise;

This shows the dates from tblReturnMerchandise (Unique Values only) with a blank field so the person can select none of the dates.

Ideally i would like the blank to be at the top, with the dates descending, but i would settle for the dates ascending. I can't get an ORDER BY statement to work on this query.

Thank you!

[pbaldy]

I don't really understand that SQL, but try

SELECT DISTINCTROW tblReturnMerchandise.ReturnDate, 1 As SortField
FROM tblReturnMerchandise
UNION
SELECT "", 0 As SortField FROM tblReturnMerchandise
ORDER BY SortField, ReturnDate DESC

[KWarzala]

You're the best! thank you so much!

[June7]

I would use Null instead of "" if this control is bound to a date/time field.

[pbaldy]

Happy to help!

[KWarzala]

I would use Null instead of "" if this control is bound to a date/time field.

It's not actually bound to anything i'm just using it to filter a form, but thanks for the suggestion

thanks!