Importing Data

[toleafs]

Hello
Any advice is appreciated...I am importing a text file into a table. I am having an issue with Date format.
The text file date is DMY. It can be shown as:

11210
171210

I am having trouble formatting to recognize the single day digit and double day digit.
Hope this makes sense.
Thanks in advance for your help

 

[Joe4]

Since it is inconsistent, I would import it as Text and them convert it using a calculation in a Query.

 

[Joe4]

That calc might look something like:

DateCalc: DateSerial(Right(Format([DateField],"000000"),2),Mid(Format([DateField],"000000"),3,2),Left(Format([DateField],"000000"),2))

where DateField is the field you are importing in Text format.

By the way, I am assuming that the month portion has leading zeroes, i.e. April 4, 2010 would be coming over as:
40510
and not
4510
or else you could have real problems!

For example, if that was the case, November 1, 2010 and January 1, 2010 would be represented the same way, i.e.
11110

 

[toleafs]

Awesome

thank you