Format Date

[Stephen Lynch]

I Have a text file that has a date field in it format like this 07152008. I
am using the import function but if I give it a date format on the import
specification it has a problem, so I import it as text.

What's the best way to convert it to a date field? Should I set up a
temporary table first and then append to the table I want it to be in after
I set the target field to date.

Should I try to change the way I write it to the text file in the format
07/15/2008 and then see if the import spec will work with date?

Any input is appreciated.

 

[Allen Browne]

After importing as a text field, you can use an Update query to populate the
real date field with an expression like this:
DateSerial(Right([d],4), Mid([d],3,2), Left([d],2)

Use your text field name in place of [d].

 

[Stephen Lynch]

Thanks Allen;

I appreciate it.

After importing as a text field, you can use an Update query to populate
the real date field with an expression like this:
DateSerial(Right([d],4), Mid([d],3,2), Left([d],2)

Use your text field name in place of [d].

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I Have a text file that has a date field in it format like this 07152008.
I am using the import function but if I give it a date format on the
import specification it has a problem, so I import it as text.

What's the best way to convert it to a date field? Should I set up a
temporary table first and then append to the table I want it to be in
after I set the target field to date.

Should I try to change the way I write it to the text file in the format
07/15/2008 and then see if the import spec will work with date?