Time Conversion Question

[Guest]

Right now the time is stored as a text field like: 081523 or 191523 for
8:15.23 am and 7:15.23 p.m.

How do i convert the text file to a date/time field? I would prefer to keep
it in military time, but the a.m./p.m. would be sufficient too.

Thanks in advance.

 

[Jeff Boyce]

Sean

An Access date/time field holds both date & time. Even if you convert
"081523" to a time value (see TimeSerial() function), what "date" portion
will you use?

How the date/time displays is independent of the storage of same. You can
decide you want to see the time portion of a date/time field in military
format -- use the Format property or function to do this.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

You should be able to use

TimeValue(Format([YourTextField],"@@@@"))

Watch out for invalid values and nulls in your text field.

 

[Guest]

Thanks John, that did the trick.

You should be able to use

TimeValue(Format([YourTextField],"@@@@"))

Watch out for invalid values and nulls in your text field.

Right now the time is stored as a text field like: 081523 or 191523 for
8:15.23 am and 7:15.23 p.m.

How do i convert the text file to a date/time field? I would prefer to
keep
it in military time, but the a.m./p.m. would be sufficient too.

Thanks in advance.

 

[Guest]

Hi John,

I have a similar scenario, but do not have seconds. Based on the function
below, I tweaked it to: TimeValue(Format([MyTextField],"@@\@"))

It produced results from "0630" to "6:30:00 AM". What would I need to do to
get rid of the seconds portion? Also, is it possible to have the am and pm
in lower case?

Thanks!!
-Valerie

You should be able to use

TimeValue(Format([YourTextField],"@@@@"))

Watch out for invalid values and nulls in your text field.

Right now the time is stored as a text field like: 081523 or 191523 for
8:15.23 am and 7:15.23 p.m.

How do i convert the text file to a date/time field? I would prefer to
keep
it in military time, but the a.m./p.m. would be sufficient too.

Thanks in advance.