date and time conversion

[Guest]

I have two text fields that indicate the date (yyyymmdd) and time (hhnnss) of
a record. Note that the time data some times has 6 or 5 characters. For
example:
ONCRDT ONCRTM
20060731 144812
20060731 144812
20060731 152532
20060731 152532
20060901 92558
20060901 93445
20060901 92717
20060901 101744
How do I combine the two fields into one so that the format is "mm/dd/yy
hh:nn:ss" or any other format that can then be recognized and used by Access?

 

[Allen Browne]

Try:
CDate(Format([ONCRDT], "0000\/00\/00\ ") & Format([ONCRTM], "00\:00\:00"))

The Format of the time with 6 digits adds the leading zero.
The character after the backslash is a literal.
The 2 concatenates strings look like a date/time value in international
format.
CDate() converts the string into a date/time value.