CODEWITHSUNDEEP
sqlms-access
Jimmy
Jimmy

Reputation: 2915

How to order an Access query by a column of times

I have a pretty basic query, but I'd like it to return the results in order from the earliest time to the latest. The column in question is a "short time." This is what I have right now:

SELECT *
FROM tasksetup
ORDER BY whenend ASC

What's the proper syntax to get this to work?

As an update: When I run this query, it works just fine in that it completes. However, the completed query is not ordered the way I'd like. It returns the results in about the same order as they are in the table, rather than ordered by whenend.

Update 2: I guess it does work. I don't know what was different yesterday, but now its ordering just fine. Oh computer science.

Upvotes: 0

Views: 1499

Answers (1)

HansUp
HansUp

Reputation: 97100

I don't see why your query doesn't work ... unless whenend includes a date component which is not visible in your format. The Jet/ACE Date/Time data type always includes an integer component to represent the date; the fractional component represents the time of day.

See what you get with this:

SELECT Format(whenend, "yyyy/mm/dd hh:nn:ss") AS full_date_time
FROM tasksetup
ORDER BY 1 ASC

Perhaps this session in the Immediate Window will help clarify Date/Time data type:

? Now()
11/5/2010 2:19:54 PM 
? Format(Now(), "short time")
14:20
? CDbl(Now())
 40487.5977546296 
? CDate(0)
12:00:00 AM 
? Format(CDate(0), "yyyy/mm/dd hh:mm:ss")
1899/12/30 00:00:00

Update: I created a table named tasksetup with a Date/Time field named whenend with the field's Format property set to Short Time. And I added 2 rows. Running your original query produces this result, which is apparently wrong:

whenend
2:00
1:00

However, the query I suggested runs without error, and reveals what is really going on:

full_date_time
1899/12/30 02:00:00
2010/11/06 01:00:00

You can use the TimeValue() function to ignore the date component of Date/Time values. Perhaps this query would give you the results you originally wanted.

SELECT TimeValue(whenend) AS ignore_dates
FROM tasksetup
ORDER BY 1 ASC;

ignore_dates
1:00:00 AM
2:00:00 AM

Upvotes: 2

Related Questions