Time Field: Sorts incorrectly

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

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

"Iram" wrote in message
...
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

Hello Again, Forgot to spell out the question:
How can I get it to sort correctly?
It looks like Access is not figuring out the correct sequential order but rather number logic.

Iram/mcp

"Iram" wrote:

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

I narrowed down the problem but I still need help...

I have a table "Master" and in that table one of the fields is called "Time".

In the properties of "Time" I have selected Text as the Data Type (Problem).

On the Lookup Tab of the Time field I have Combo Box, Table/Query, Table Name "tbl_Time".
So in essence Master is looking at tbl_Time for the time range.
The time also sorts well in the tbl_Time. And if I run a query off of this table directly the time range is OK.

But like I said the field Time on the Master table is set to Text as the Data Type.
This is the reason why its sorting as if it were text.
If I set the Data Type to Date/Time I can not pull the time range from the other table.
How can I get aroun this?

Iram/mcp









"Iram" wrote:

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

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





"Roger Carlson" wrote:

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

"Iram" wrote in message
...
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

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.


--
Good luck

Jeff Boyce
Access MVP