By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Sorting Short Times in Access Query

P: 30
I have the following query in my time clock database which I use to display times entered for a specific date range.

Expand|Select|Wrap|Line Numbers
  1. SELECT IIf(Weekday([ClockInDate])=2,"Monday",IIf(Weekday([ClockInDate])=3,"Tuesday",IIf(Weekday([ClockInDate])=4,"Wednesday",IIf(Weekday([ClockInDate])=5,"Thursday",IIf(Weekday([ClockInDate])=6,"Friday",IIf(Weekday([ClockInDate])=7,"Saturday","Sunday")))))) AS WeekdayIN, tblPAYROLL_REPORTS.ClockInDate, tblPAYROLL_REPORTS.TimeIn, IIf(Weekday([ClockOutDate])=2,"Monday",IIf(Weekday([ClockOutDate])=3,"Tuesday",IIf(Weekday([ClockOutDate])=4,"Wednesday",IIf(Weekday([ClockOutDate])=5,"Thursday",IIf(Weekday([ClockOutDate])=6,"Friday",IIf(Weekday([ClockOutDate])=7,"Saturday","Sunday")))))) AS WeekdayOUT, tblPAYROLL_REPORTS.ClockOutDate, tblPAYROLL_REPORTS.TimeOut, tblPAYROLL_REPORTS.TimeType, tblPAYROLL_REPORTS.TotalHours, tblPAYROLL_REPORTS.EmployeeNo, IIf([Edited]=True,"##","") AS Expr1
  2. FROM tblPAYROLL_REPORTS
  3. ORDER BY tblPAYROLL_REPORTS.ClockInDate, tblPAYROLL_REPORTS.TimeIn;
  4.  
The times are formatted as Short Time and display as 07:00:00 AMPM. So ascending order starts at 00:00:00 and ends at 12:59:59. In this scenario, 01:00:00 pm would come BEFORE 07:00:00 am.

If someone clocks IN at 07:00:00 am and back IN at 01:00:00 pm after lunch, my sort for that field needs to be DESCENDING to display the times chronologically.

However if that person clocks in at 07:00:00 am and back in at 11:00:00 am after lunch, that time field need to be sorted ASCENDING.

What do I need to do in order for Access to recognize that 01:00:00 PM is AFTER 07:00:00 AM so that my report will list these times in order?
Jul 19 '12 #1

✓ answered by Rabbit

If the data type is actually a date time data type, it will sort correctly regardless of display format. If you're using a text data type and it just looks like a time, then you will have trouble sorting unless you convert the field to a date time data type.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,315
If the data type is actually a date time data type, it will sort correctly regardless of display format. If you're using a text data type and it just looks like a time, then you will have trouble sorting unless you convert the field to a date time data type.
Jul 19 '12 #2

P: 30
Thank you! I was formatting the text data type as a date, instead of storing the data as date time.
Jul 19 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.