Date/time field/column need query to have distinct dates (without times) & sorted correctly

Question

I have a list of records that have a date/time field/column; I want a query to get just the distinct days (removing the time, like 1/1/14, 1/2/14, etc) & sorted correctly. Its going into a form's combo box row source so a user can filter/narrow-down the info on the screen per day

Here is my original query:

SELECT TOP 1 "*** ALL ***" AS ord_tdate FROM qryOrderEntriesNotBilled 
UNION SELECT DISTINCT FORMAT(ord_tdate, "mm/dd/yy") FROM qryOrderEntriesNotBilled
ORDER BY 1;

Here is a simplified view for the sake of the question at hand:

SELECT DISTINCT FORMAT(ord_tdate, "mm/dd/yy") FROM qryOrderEntriesNotBilled
ORDER BY 1;

Both of the above work, but the order is wrong, its obviously sorting by number (1st one) rather than overall value as seen here:

Things I have tried unsuccessfully:

• DATEVALUE(ord_tdate) solved the simplified view, but with original query (union) it doesn't work correctly as seen below

• CDate(Datevalue(ord_tdate)) & CDate(FORMAT(ord_tdate, "mm/dd/yy")) had same results as above

  

Answer 1

Include a second field expression, DateValue(ord_tdate), in the row source query. Sort by that field. In the combo properties, select 2 as the column count and set the column width for that second column to zero.

This query returns what I think you want in Access 2007 with qryOrderEntriesNotBilled as an actual table instead of a query.

SELECT DISTINCT
    FORMAT(ord_tdate, "mm/dd/yy") AS date_as_text,
    DateValue(ord_tdate) AS date_as_date
FROM qryOrderEntriesNotBilled
UNION ALL
SELECT TOP 1
    "*** ALL ***",
    Null
FROM qryOrderEntriesNotBilled
ORDER BY 2;

My personal preference is to use a custom single-row Dual table for the "fabricated" query row. If you would like to try that approach you can find a procedure to create your own here. But a dedicated table is absolutely not a requirement for this. It's purely a matter of developer preference.