Using ORDER BY with a time field ignores am and pm

[sweetp]

Hi everybody,

I need to sort on a time field:

ORDER BY StartTime ASC

but it ignores AM and PM and instead sorts just on the numbers, so for example 04:00 pm comes before 12:30 pm, but that's not the way it should be.

What do I need to do??

Thanks a bunch!

 

[scriverb]

Check the field type of the supposed date/time field and see if it is not actually a text field.

Post back if this is not the problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sweetp]

It is formatted in Access as a Date/Time field with Medium Time.

 

[scriverb]

How did you enter the data in this field? Was it key entry, Now(), Time(), or derived from a calculation?

Even though it is displayed as Medium Time if the field holds any Date information along with the time information then the sort can be affected.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sweetp]

The info is originally entered with an applet where you click and drag to choose the time and it enters in into a textfield (input=text), but how can it be a textfield if I can format it in Access to show time?

Anyway, is there a way to format the way I want, given this situation? Thanks so much for your help!

 

[scriverb]

I thought so. You can convert the text field to Time by use of the DateValue function. This function will take a text representation of a date or time and convert it to a true Date or Time value. This value can be updated back into your table in a new Date/Time field which then can be renamed and everything from that point will work properly or you can just perform the DateValue whenever you need it.

Look up DateValue function is ACCESS help to better understand the function.

Post back if you have more questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sweetp]

I'll try this and see what happens. Thanks again!

 

[sweetp]

Well, I can't figure out how to apply it (I'm pretty new at this), so here's my code in my Query (before I tried to do anything).

SELECT Conference_Room_Schedule.ConfRoomSchedID, Conference_Room_Schedule.ConfRoomID, Conference_Room_Schedule.SchedDate, Format([Conference_Room_Schedule]![StartTime],"Medium Time") AS StartTime, Format([Conference_Room_Schedule]![EndTime],"Medium Time") AS EndTime, Conference_Room_Schedule.ForPersonnelID, Conference_Room_Schedule.Notes
FROM Conference_Room_Schedule;

It is the StartTime and EndTime fields that I need to apply the DateValue (or do I really need to use TimeValue?). The "Medium Time" was inserted in there by Access when I formatted the actual table field to show medium time, I believe, so my fingers didn't actually type that.

I really hope this is easy. And thank you again if you can help.

Donna (sweetp)

 

[Golom]

Try
[tt]
SELECT ConfRoomSchedID,
ConfRoomID,
SchedDate,
Format([StartTime],"Medium Time") AS StartTime,
Format([EndTime],"Medium Time") AS EndTime,
ForPersonnelID,
Notes

FROM Conference_Room_Schedule

ORDER BY cDate(StartTime)
[/tt]

 

[sweetp]

It still goes in the order of the numbers instead of putting am before pm.

Example:

04:15 p.m.
05:50 p.m.
09:00 a.m.
12:00 p.m.

FYI, I'm using jsp/dreamweaver for the search and results pages to connect to the Access database.

Thanks for any more help!

 

[sweetp]

Hey it does work!

I originally put the code in the Acces query instead of including it in my Dreamweaver recordset. It works! This is great! THANK YOU SO MUCH, Golom!!!!!!!!!

 

[scriverb]

Give this SQL a try:

SELECT Conference_Room_Schedule.ConfRoomSchedID, Conference_Room_Schedule.ConfRoomID, Conference_Room_Schedule.SchedDate, Format([Conference_Room_Schedule]![StartTime],"Medium Time") AS StartTime, Format([Conference_Room_Schedule]![EndTime],"Medium Time") AS EndTime, Conference_Room_Schedule.ForPersonnelID, Conference_Room_Schedule.Notes
FROM Conference_Room_Schedule
ORDER BY[red] Format(TimeValue([Conference_Room_Schedule]![StartTime], "Medium Time"))[/red];

Post back with the results please.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sweetp]

I get "wrong number of arguments used" error message. The cDate code above worked, though. Any reason that I shouldn't use that?

thanks!

 

[scriverb]

sweettp: Yes, I clobbered that one. I let your posting influence me into the TimeValue function. You can use DateValue or CDate in this situation and it will convert a string time or date into the appropriate sortable value respectively. So just change that TimeValue to DateValue or CDate and you are in business. I guess I was posting at the same time as Golom. Didn't mean to step on your answer there.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[sweetp]

scriverb,

Thanks for your help. I appreciate any and all I get! And I'm sure it won't be long until I'm looking for more help on something else. Thanks again.