Help on Importing a Date/Time Data Type

[Guest]

I have a program that exports data into an excel spreadsheet that I want to
import into Access. I can get the file imported into a table without any
problems. However, two of the fields are a Date Time Group in the following
format: dd hhnnZ MMM YY; where the Z is a time zone identifier. Is there any
way to get Access to recognize this as a Date/Time Data type instead of text?

 

[John Nurick]

Hi Philippe,

I have a program that exports data into an excel spreadsheet that I want to
import into Access. I can get the file imported into a table without any
problems. However, two of the fields are a Date Time Group in the following
format: dd hhnnZ MMM YY; where the Z is a time zone identifier. Is there any
way to get Access to recognize this as a Date/Time Data type instead of text?

No. If you want to preserve the time zone information, add two fields to
the table, a date/time field for the date and a text or numeric field
for the time zone information (depending on how you want to handle it).
Otherwise just use one date/time field.

Then you can use either an update query with expressions or custom VBA
functions to parse the imported text field and generate the values for
the new field(s). Typically one would use the Left(), Mid() and Right()
functions to parse the string into chunks for day, hour, minute, month
and year, and then use DateSerial(...) + TimeSerial() to produce the
Date/time value.

 

[Guest]

I got the Left() Mid() Right() Functions down and have broken my DTG down
into is individual parts. My Month is reported as three letters and for the
DateSerial(..) to work it needs to be two numbers. I thought I could use
DateValue() to convert it so DateSerial(..) will work but I can't bring all
the pieces back together to use DaveValue(). What function can I use to put
my Date back together?

 

[Guest]

I didn't notice the MMM. Instead of using DateSerial, use Left, Mid and Right
to assemble the date into a string that CDate can handle. E.g., from the
Immediate pane:

S = "20 1807Z MAR 07"
?CDate(Left(S,2) & " " & Mid(S, 10, 3) & " " & Right(S,2)) +
TimeSerial(Mid(S, 4,2), Mid(S, 6,2), 0)
20/03/2007 18:07:00

 

[Guest]

That got it. Thank you very much.

I didn't notice the MMM. Instead of using DateSerial, use Left, Mid and Right
to assemble the date into a string that CDate can handle. E.g., from the
Immediate pane:

S = "20 1807Z MAR 07"
?CDate(Left(S,2) & " " & Mid(S, 10, 3) & " " & Right(S,2)) +
TimeSerial(Mid(S, 4,2), Mid(S, 6,2), 0)
20/03/2007 18:07:00