Text to date conversion

[SandySun]

I am importing a .txt file into Access, and the date displayed (imported) is
71408....I tried to import the file using the wizard and defaulting the field
as a date field....however it did not work....I had to import the field as a
text. Does anyone know how I can convert the text field to a date field?
07/14/08 I tried using the append query, however I'm stuck.

Thanks in advance!

 

[Jeff Boyce]

So, is July 2, 2008 shown as 7208? Your "text" field is not a date/time
field and may not be readily convertable without more work...

How do you know how to break it apart into month, day and year? That's what
you have to tell Access how to do.

Take a look at the Left(), Mid() and Right() functions, and at the
DateSerial() function.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SandySun]

Yes Jeff it is July 2, 2008.....but because I am missing the leading 0, the
date conversion using DateSerial is all messed up.....any suggestions?
Thanks!

 

[pietlinden]

Yes Jeff it is July 2, 2008.....but because I am missing the leading 0, the
date conversion using DateSerial is all messed up.....any suggestions?  
Thanks!

This is going to be challenging, only because you need to give more
info. Are the day and year always two digits? If so, you can do
something like

MyFixedDate:IIF(LEN(MyDate)=5,"0" & MyDate,MyDate)
(if MyDate is 5 characters long, stick the leading zero back on.)

then you can use something like this:

DateSerial(cint(right$(MyFixedDate,2)),cint(Left$(MyFixedDate,
2)),cint(Mid$(MyFixedDate,3,2)))

to convert "MyFixedDate" to a real date.

 

[SandySun]

Ok I entered the code as - IIf(Len([Date1])=5,"0" & [Date1],[Date1]), however
it did not work (I entered it in an update query). any suggestions?

 

[SandySun]

I got it to work...with your help of course =)....I just had to change the
field it was appending too (a temporary field) to a text instead of a date.
Thanks for your help Piet....you are wonderful!

Ok I entered the code as - IIf(Len([Date1])=5,"0" & [Date1],[Date1]), however
it did not work (I entered it in an update query). any suggestions?

This is going to be challenging, only because you need to give more
info. Are the day and year always two digits? If so, you can do
something like

MyFixedDate:IIF(LEN(MyDate)=5,"0" & MyDate,MyDate)
(if MyDate is 5 characters long, stick the leading zero back on.)

then you can use something like this:

DateSerial(cint(right$(MyFixedDate,2)),cint(Left$(MyFixedDate,
2)),cint(Mid$(MyFixedDate,3,2)))

to convert "MyFixedDate" to a real date.