Sorting time values 1

[evergreean43]

My time values from a field in my Access 2000 database wont sort correctly after I attempted to order them in my SQL:
11:2:10 A
11:36:35 A
12:37:54 P
12:37:56 P
7:46:12 A
8:44:18 A
9:52:50 A


My sort order results should look like this:
12:37:56 P
12:37:54 P
11:36:35 A
11:2:10 A
9:52:50 A
8:44:18 A
7:46:12 A


Here is my SQL:

select *
from myTable
order by theTime asc

 

[rjoubert]

What is the data type of your field? If it's just a text field, you would need to change it to a Date/Time field. On the other hand, if it is already a Date/Time field, you may want to change it's format to see what the Date portion of it is. Even though you don't see the Date portion, it may be there, and it will sort by the Date portion before it sorts by the Time portion.

 

[evergreean43]

Its a Text field.

I dont have access to or permissions to change the date field type.

Can I sort it correctly as a text field??

 

[evergreean43]

Forget my last question, I will sort it by my primary key and that will work.

Thanks

 

[rjoubert]

Wrap a CDate function around your field in the Sort By clause of your SQL...

select *
from myTable
order by CDate(theTime) asc

 

[evergreean43]

Thanks