Bug in MS Access!! Grouped By date field does not sort properly.

[WeebleSue]

I have entries in my table that occur several times per day. I have created a new Query that Groups By the date field. I want to show only the maximum number that occurs of the entries for that day. But what I have found in the results is that the Grouped By field sorts wrong! It sorts
1/1
1/10
1/11
1/12
1/13
1/2
1/20
1/21
1/22
1/3
1/30
1/4

etc.! I try to re-sort on that column but nothing changes.

The information in that column is also left-justified, which indicates to me that Access no longer recognizes the information as "date." Dates are typically right justified. The query design specifically has a Format statement that uses "Short Date." So why aren't the results viewed as actual dates?

The original information is most certainly a date (right justified, sorts properly, I can do date functions on it, etc.).

I am using MSAccess 2010.

Does anyone have any ideas on what i can do differently in this query to get the data sorted properly?

I have attached the original table, the query definition (generated by the query wizard), and the results.

 

[plog]

Let this be a lesson to everyone: A guaranteed way to get me to drop everything and read your post is to mention how you found a bug in Access.

This isn't really a bug, its more of a misunderstanding/bad assumption on your part--helped by Access making it easy for you make that misunderstanding/assumption. Format() returns a string value. Hey, I'm with you, if you specify a date format then it should return a legitimate date value, but it does not. What you think are dates are in fact strings (one way to verify this is to look at the data set--anything left justified is a string and anything right justified is a date or number). When sorting strings, 1/21 comes before 1/3. So its doing the sorting right.

What you need to do is make that string a date. One way to do this is to put your date value inside a CDate call like this:

Timestamp By Day: CDate(Format([Usage].[Timestamp], 'Short Date'))

Do that and your query should sort correctly.

 

[WeebleSue]

Dang, and I thought I found a bug!! I still think that MS could do a better job when creating their own queries (a'la using the Wizard) - you would think that if i am using dates, trying to sort or group by dates, that their own wizard would know that i want the results in date format as well, and put the CDate in themselves.

But alas, they do not... and it leaves us poor saps to rely on the kindness of extremely knowledgeable and generous strangers like yourself.

I am SO much obliged. I sincerely thank you for the clarification.

The new formula works perfectly.
sending warm hugs from Houston,
Susan

 

[Brianwarnock]

I prefer to use Datevalue(time stamp) to extract the date part of a date-time field.

Brian