Text or number to short time.

[quinto via AccessMonster.com]

I have linked an XLS file to a dataabse and I need to convert the following
to short time
Appt Time: 815 (Number), Return Time: 1455 (Custom hmm).
I have no control how the xls file is formatted or what field are made
available, I will use a query to select about 5 fields and uppned them to a
table.
Also I would like to convert Apt Date: 20070101 (Number) to date 01/01/2007.

Thanks

Quinto

 

[John Spencer]

Convert the time using
TimeValue(Format(1815,"##:##"))

Convert the date using
DateValue(Format(20070101 ,"####/##/##"))

Of course, you will need to test for nulls/blanks. You can probably do that
with
IIF (IsDate(Format(20070101 ,"####/##/##")),DateValue(Format(20070101
,"####/##/##")),Null)
IIF(IsDate((Format(1815,"##:##")),TimeValue(Format(1815,"##:##")),Null)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[quinto via AccessMonster.com]

Thanks for your help

Quinto

Convert the time using
TimeValue(Format(1815,"##:##"))

Convert the date using
DateValue(Format(20070101 ,"####/##/##"))

Of course, you will need to test for nulls/blanks. You can probably do that
with
IIF (IsDate(Format(20070101 ,"####/##/##")),DateValue(Format(20070101
,"####/##/##")),Null)
IIF(IsDate((Format(1815,"##:##")),TimeValue(Format(1815,"##:##")),Null)

I have linked an XLS file to a dataabse and I need to convert the
following

[quoted text clipped - 10 lines]

Quinto