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

[kev100 via AccessMonster.com]

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

 

[Jerry Whittle]

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

 

[kev100 via AccessMonster.com]

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

 

[John Nurick]

Are you filtering on

CDate("5:30 PM") ?


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

 

[kev100 via AccessMonster.com]

John...

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

CDate([ENTER TIME:])

....is used.

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

Thanks very much all.