Sorting MS Access Dates Desc

Question

I keep a table for my work and I like to know how much I have accomplished each day. Notable fields are VisitID (autonumber) to identify the case and WorkDate (dafaults to Now()). My Totals query counts DisitID and Group By WorkDate formatted to mm-dd-yyyy Descending. And I lived happily thereafter with each day's record on top. Until Dec 31, 2025.

On 1/1/2026, I found my record for the day at the bottom of the query, and today's (1/2/2026) also at the bottom but on top of 1/1/2026:

Why do I have the records sorted properly until Dec 31, 2025, only for Access to change its mind? I know I did something wrong, but I cannot figure it out.
Please help.

Answer 1

Unfortunately, Access doesn't support CTEs. That would be handy.

Because using Format() on dates converts the result from a date value to a string value, you have to be careful how you do that, as Erland pointed out.

There are probably 3 or 4 ways to get the result you want in Access.

I might do it this way. Or you could try a different order by that doesn't rely on formatting the date.

SELECT Format([YourDateFieldGoesHere], "mm-dd-yyyy") AS DOS,
COUNT(*) AS DailyCount
FROM  tblYourTableNameGoesHere
GROUP BY Format([YourDateFieldGoesHere], "mm-dd-yyyy"),
         Format(tblIndividual.Birthdate, "YYYYmmdd")
ORDER BY Format(tblIndividual.Birthdate, "YYYYmmdd") DESC;

Answer 2

You don't show your query, but it sounds like you are doing something like this:

SELECT formatdate(date, mm-dd-yyyy)), COUNT(*)
FROM  tbl
GROUP BY formatdate(date, mm-dd-yyyy
))
ORDER BY formatdate(date, mm-dd-yyyy)) DESC

That is, you are ordering by a string, note a date. And strings that start with 01 sorts before those starting with 31. Or after, in this case, as you do it DESC.

Now, I don't know Access, but I happened to see you query when I reviewed posts that had fallen to victim for the spam filter. But in SQL Server (which is my field of expertise), you would do this with a CTE:

WITH CTE AS (
    SELECT date, COUNT(*) AS CountOf
    FROM  tbl
    GROUP BY date
)
SELECT formatedate(date, mm-dd-yyyy), CountOf
FROM  CTE
ORDER  BY date DESC

I don't if this runs in Access, but you could try.

(formatdate is just something I made up. Keep on using what you are using now.)