Query sort is unpredictable

Topic

New Reply

I really thought this would be pretty simple . . .

I have a query with a date entry and a time entry. I want to sort first by date and then by time in descending order:

8/1/2011 14:23
8/1/2011 13:17
8/1/2011 11:05
7/7/2011 16:18

It’s not doing that. It sorts by date in descending order just fine, but the time results are really unpredictable. Here is something like the results I get:

8/1/2011 13:17
8/1/2011 14:23
8/1/2011 11:05
7/7/2011 16:18

I have both fields set as “descending” in the sort setting. How difficult can this be? I even tried this in the report using group/sort and get the same kind of (unpredictable) time results.
28643-8-7-2011-5-51-57-PM

Reply | Quote

Replies

I really thought this would be pretty simple . . .

I agree. What you are doing looks OK assuming the Date field really contains what you think it does.
Date and Time fields are the same data type, with whole numbers representing the days and the decimal part the time.

So I am wondering if your Dates have something (perhaps hidden) represeting a Time in them. How does the data get into the Date field?
Do you have a default value? What is it? (e.g. if the Default was Now() Access would put the current time into the data field.)

Can you perhaps post a bit of sample data?

Reply | Quote

The default values are Date() and Time()Sample date from query attached

Reply | Quote

According to Access 2007 help the sort order is as I suspected; it sorts the first field first, the second field second, the thir . . . .But that’s not what I’m getting (all the time anyway). Check out these expanded results; everything looks good until you hit 7/20, and then it looks OK until 7/13. If I run the query again, the results remain the same. I also checked the input mask and found that it is “Short Time”, but no input mask for date because the Date Picker is utilized.

Reply | Quote

Can you post the actual sample data and your query rather than just a picture of it?

Reply | Quote

Can you post the actual sample data and your query rather than just a picture of it?

Not in any way to discount your request John, but I found the problem. The default values were just as I described and I thought the only input method was by the default value in the table settings, but I have since discovered FrmJournalNew where, as you suspected, the default value is Now(). Going through the records I see (by clicking in the cell) where the vast majority are short time, but that some contain date and time to the second. There’s only a few hundred records to sort through so I’ll just edit manually and disable the date and time entry in the form.Thanks for the help!

Reply | Quote

Try changing the format of the Date field to mm/dd/yyyy h:nn.

This will show you if there are actually times in there after the dates.

Reply | Quote

Glad you found it.

Reply | Quote