Date format using pre-defined specification in Access?

[Guest]

When attempting to import a text file using a pre-defined Import
Specification file that contains a Date field, the resulting table does not
have the corresponding field is not of the type Date/Time and the data is
brought in as text.

Example: 02051998 should be brought in as 02/05/1948 but comes in exactly
the same as in the text file.

Thanks.

 

[Dirk Goldgar]

When attempting to import a text file using a pre-defined Import
Specification file that contains a Date field, the resulting table
does not have the corresponding field is not of the type Date/Time
and the data is brought in as text.

Example: 02051998 should be brought in as 02/05/1948 but comes in
exactly the same as in the text file.

I don't think you can make Access recognize 02051988 as a date on
import. You will probably have to link to the text file using an import
spec that defines that as a text field, then use an append query to
transform the linked records and put them into your target table (which
you would already have defined).

In the query that pulls data from the linked text file, use a calculated
field that converts the mmddyyyy field into a date field; e.g.,

NewDate: CDate(Format([TextDate], "00/00/0000"))

Then stuff the calculated field into the target date field.

 

[Guest]

Thanks Dirk. That explains it all.

When attempting to import a text file using a pre-defined Import
Specification file that contains a Date field, the resulting table
does not have the corresponding field is not of the type Date/Time
and the data is brought in as text.

Example: 02051998 should be brought in as 02/05/1948 but comes in
exactly the same as in the text file.

I don't think you can make Access recognize 02051988 as a date on
import. You will probably have to link to the text file using an import
spec that defines that as a text field, then use an append query to
transform the linked records and put them into your target table (which
you would already have defined).

In the query that pulls data from the linked text file, use a calculated
field that converts the mmddyyyy field into a date field; e.g.,

NewDate: CDate(Format([TextDate], "00/00/0000"))

Then stuff the calculated field into the target date field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)