Convert part of a text field to a time value

[Guest]

This is driving me nuts!

I have text field [APPT_DT_TM)which actually contains a concatenated date &
time value e.g

200605021541

= 2nd May 2006, 15:41

I want to use that to create two new fields in a query, one holding the
date, one holding the time.

I've got the date, using

APPT_DATE:
DateSerial(Left([APPT_DT_TM],4),Mid([APPT_DT_TM],5,2),Mid([APPT_DT_TM],7,2))

And I've faked a time field with

APPT_TIME: Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2)

But how can I get the time part to be an actual time value for calculation
purposes?

Regards

Matt

 

[John Spencer]

TimeValue should convert any valid string into a time.
TimeValue(Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2))

I would probably keep the entire date and time in one field. Assuming the
that Appt_DT_TM is never null the following works for me

CDate(Format("200605021541","@@@@/@@/@@ @@@"))

You can always use DateValue or TimeValue against that value. Of course
that is an extra step if you never need them combined.

 

[Guest]

Wow, quick reply thank-you! The TimeValue worked great, thank-you. There are
null values in this field, so I will get #Error but that's OK in this
instance.

I've asked before but forgotten, how do I get a full description of what
each function like TimeValue, CDate etc actually does?

Matt

TimeValue should convert any valid string into a time.
TimeValue(Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2))

I would probably keep the entire date and time in one field. Assuming the
that Appt_DT_TM is never null the following works for me

CDate(Format("200605021541","@@@@/@@/@@ @@@"))

You can always use DateValue or TimeValue against that value. Of course
that is an extra step if you never need them combined.

This is driving me nuts!

I have text field [APPT_DT_TM)which actually contains a concatenated date
&
time value e.g

200605021541

= 2nd May 2006, 15:41

I want to use that to create two new fields in a query, one holding the
date, one holding the time.

I've got the date, using

APPT_DATE:
DateSerial(Left([APPT_DT_TM],4),Mid([APPT_DT_TM],5,2),Mid([APPT_DT_TM],7,2))

And I've faked a time field with

APPT_TIME: Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2)

But how can I get the time part to be an actual time value for calculation
purposes?

Regards

Matt

 

[Marshall Barton]

This is driving me nuts!

I have text field [APPT_DT_TM)which actually contains a concatenated date &
time value e.g

200605021541

= 2nd May 2006, 15:41

I want to use that to create two new fields in a query, one holding the
date, one holding the time.

I've got the date, using

APPT_DATE:
DateSerial(Left([APPT_DT_TM],4),Mid([APPT_DT_TM],5,2),Mid([APPT_DT_TM],7,2))

And I've faked a time field with

APPT_TIME: Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2)

But how can I get the time part to be an actual time value for calculation
purposes?

Use TimeSerial

But why do you want this in two fields? A dateTime field
can contain both, which is almost always easier to use in
calculations. Your text field can be coverted to a date
time value in one step:

APPT_DATE:
CDate(Format(APPT_DT_TM, "@@@@\-@@\-@@ @@\@"))

 

[John Spencer]

I always get it from the Help files. I often find it is easiest to switch
to a vba window and type in the function, highlight it, and then press F1
key.

If you want to look through the help, just press F1 in a code window and
then select the contents tab and them functions and look through all the
available ones.

Wow, quick reply thank-you! The TimeValue worked great, thank-you. There
are
null values in this field, so I will get #Error but that's OK in this
instance.

I've asked before but forgotten, how do I get a full description of what
each function like TimeValue, CDate etc actually does?

Matt

TimeValue should convert any valid string into a time.
TimeValue(Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2))

I would probably keep the entire date and time in one field. Assuming
the
that Appt_DT_TM is never null the following works for me

CDate(Format("200605021541","@@@@/@@/@@ @@@"))

You can always use DateValue or TimeValue against that value. Of course
that is an extra step if you never need them combined.

message

This is driving me nuts!

I have text field [APPT_DT_TM)which actually contains a concatenated
date
&
time value e.g

200605021541

= 2nd May 2006, 15:41

I want to use that to create two new fields in a query, one holding the
date, one holding the time.

I've got the date, using

APPT_DATE:
DateSerial(Left([APPT_DT_TM],4),Mid([APPT_DT_TM],5,2),Mid([APPT_DT_TM],7,2))

And I've faked a time field with

APPT_TIME: Mid([APPT_DT_TM],9,2) & ":" & Mid([APPT_DT_TM],11,2)

But how can I get the time part to be an actual time value for
calculation
purposes?

Regards

Matt