Query TIME saved as TEXT to Sort PROPERLY (e.g. 1:36 PM to list after 10:00AM) ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kev100 - 29 Jun 2006 17:07 GMT

I have a field in a table which stores TIME....but the field HAS to be TEXT,
unfortunately

The format can be Either 24hr (e.g. 10:00 , 12:35, 13:45, etc)

or

Regular 12hr (e.g. 10:00 AM, 12:35 AM, 1:45 PM, etc).

The problem is that I have a report based on a query that will sort the
records by TIME...based on what the user inputs when prompted - the user is
prompted for a certain time...and the query will return all records created
AFTER that time (TimeCreated > TimeEnteredAtQuery)

It would be best, by far, for the user to be able to enter regular 12hr
format into the prompt (e.g. 10:05 AM or 2:06 PM)...having "mentally convert"
to 24hr will simply increase the error/hassle rate.

Currenly.....recording time in 24hr format AND entering 24hr format at the
prompt works fine...

However....when using 12hr AM/PM format to record the data AND 12 hr AM/PM
format at the prompt will ignore AM/PM.

For example. A sort that sez: "Show all records created after 10:30 PM
(TIME>"10:30PM") will only list records from 10:31 AM to 12:59 PM and 10:31
PM to 12:59 AM. It needs to only display records created from 10:31PM to 12
Midnight.

I'm presumming this mis-sort is due to the time field being TEXT.

I'm hoping that there is some coding / setting in Access that can somehow
compensate for this so that the User and enter 12hr AM/PM a the prompt and
have the query properly sort.

And...again...the time can be recorded in either 12 or 24hr format...but
unfortunately HAS to be stored as Text.

Any advice greatly appreciated.
THANKS

Reply to this Message

Jerry Whittle - 29 Jun 2006 20:06 GMT

First run a query on the "time" field to find out which records can not be
considered Time.

Expr1: IsDate([TimeTextField])
Criteria = 0

If any records show up, they MUST be fix before you have an ice cube's
chance in Hades.

Once you get them fixed, you can use the CDate function to convert these
"times" to actual time. Then you can sort them just like real time fields.
Actually you can do this all at once like below:

RealTime: Iff( IsDate([TimeText]) = -1, CDate([TimeText]), #23:59#)

Anything that can't be evaluated as a valid time will be pushed off to the
end with 23:59. Or you could change it to #00:00# so that they show up at the
front.

It goes without saying, but that won't stop me, that storing time in a
Date/Time field will stop grossly bad data entry mistakes and keep you from
jumping though hoops like IsDate and CDate functions

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a field in a table which stores TIME....but the field HAS to be TEXT,
> unfortunately
[quoted text clipped - 36 lines]
> Any advice greatly appreciated.
> THANKS

Reply to this Message

kev100 - 30 Jun 2006 20:40 GMT

Jerry,

Thanks very much....the

RealTime: Iff( IsDate([TimeText]) = -1, CDate([TimeText]), #23:59#).....works
great.

It also seems to sort correctly (e.g. ascending will put 5:40 AM before 5:30
PM.....and Descending will put 6:35 PM before 6:45 AM).

However....oddly...when filtering by > "5:30 PM"....the return list will
include 5:40 AM...or anything greater than 5:30....both AM and PM)

The end result I'm hoping for is to be able to prompt the user for a time....
allow them to enter the time in good 'ol 12 hr AM/PM format and have the
query display all records created AFTER that time. The criteria used in the
query will be simply RealTime > [UserEnteredTime in 12hr AM/PM format].

Again...what's odd is that the RealTime field will produced correct results
when using the default "Ascending" or "Descending"...but the ">" filter
criteria seems to ignore AM/PM.

Thanks

Reply to this Message

John Nurick - 01 Jul 2006 07:04 GMT

Are you filtering on
> CDate("5:30 PM")
?

>Jerry,
>
[quoted text clipped - 19 lines]
>
>Thanks

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

kev100 - 03 Jul 2006 00:06 GMT

John...

Thanks very much....that CDate usage did the trick.

On the RealTime field.....the filtered input:

>CDate([ENTER TIME:])

...is used.

It allows 12hr AM/PM input then produces all records created After that
entered time.

Thanks very much all.

Reply to this Message