Time Field: Sorts incorrectly

[Iram]

Hello,
I have created a table with a field called "Time". The Format is Medium Time with an Input Mask of 09:00\ >LL;0;_


I have created the following records in the table:

8:00:00 AM
8:30:00 AM
9:30:00 AM
10:00:00 AM
10:30:00 AM
11:00:00 AM
11:30:00 AM
12:00:00 PM
12:30:00 PM
1:00:00 PM
1:30:00 PM
2:00:00 PM
2:30:00 PM
3:00:00 PM
3:30:00 PM
4:00:00 PM
4:30:00 PM
5:00:00 PM

Once I sort these times in a query or report they sort as:

1:00:00 PM
1:30:00 PM
10:00:00 AM
10:30:00 AM
11:00:00 AM
11:30:00 AM
and so on....

Even the Reports that sort by time don't sort correctly.
This table acts as a Combo Box table/query field in a master table.

Your help is greatly appreciated,
Iram/mcp

 

[Roger Carlson]

I am not seeing this behavior in my test file. Are you using the Format()
function in your query? That will convert you time to a string value and
will show the kind of behavior you describe.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Hello,
I have created a table with a field called "Time". The Format is Medium

Time with an Input Mask of 09:00\ >LL;0;_

 

[Iram]

All taken care of.

I set the Master table, Time field to Date/Time Medium Time. Then I set the forms to query the Time Table to pull the time range and insert it into the table.

Iram/mcp

 

[Jeff Boyce]

Iram

MS Access treats "Time" as a reserved word, naming a function. Also "Date"
and several others. Perhaps an issue you are running into is related to
Access' confusion about what you mean by naming a field "Time".

Also, Access treats a Date/Time field as a "point-in-time", not a duration.
If you have stored what you believe is "8:00:00 AM", you have actually
stored a date plus that time.