converting atomic fields to a single date time field

[Guest]

In importing a dbf file into Access, I have run into the following problem.
There is a numeric 2 digit mo field, a numeric 2 digit day field, a numeric 4
digit year field, and 4 character time field with leading 0 when necessary.
I can build an expression that creates the Short Date and Short Time format
within a single field (without the colon in Time). However, I am having
problems getting Access to recognize this as a date time field. Everything
is left justified in the created field, whereas when I build a data time
field from scratch with Short Date and Short Time, everything is right
justified. Is there a straightforward expression that will combine the mo,
day, year, and time fields into a single date time field formatted by Short
Date and Short Time?

Thanks

 

[John Vinson]

In importing a dbf file into Access, I have run into the following problem.
There is a numeric 2 digit mo field, a numeric 2 digit day field, a numeric 4
digit year field, and 4 character time field with leading 0 when necessary.
I can build an expression that creates the Short Date and Short Time format
within a single field (without the colon in Time). However, I am having
problems getting Access to recognize this as a date time field. Everything
is left justified in the created field, whereas when I build a data time
field from scratch with Short Date and Short Time, everything is right
justified. Is there a straightforward expression that will combine the mo,
day, year, and time fields into a single date time field formatted by Short
Date and Short Time?

Thanks

Yes:

CDate([mo] & "/" & [day] & "/" & [year] & " " & Format([time],
"@@@"))

This will convert 05, 24, 2005, 2325 to "05/24/2005 23:25", and CDate
will convert this text string to an Access Date/Time value (which can
then be formatted any way you please; it's stored as a Double Float
number, and the format merely controls how it's displayed).

John W. Vinson[MVP]

 

[Tim Ferguson]

I can build an expression that creates the Short Date and Short Time
format within a single field (without the colon in Time). However, I
am having problems getting Access to recognize this as a date time
field.

UPDATE MyTable
SET MyDateTime = DateSerial([Years], [Months], [Days]) +
TimeSerial([Hours], [Minutes], 0)
WHERE MyDateTime IS NULL

This works everywher because the DateSerial and TimeSerial functions are
not sensitive to regional settings.




B Wishes


Tim F