Import Table - Date Problem

[Tieval]

I am manually importing a txt file to save the method for use with transfertext but cannot seem to get around a problem with the date format even when doing it manually.

I wish to import a field as a date and time with the original date as YMD.

In my txt file the dates are stored as for example 2015/04/22 for the twenty second of April 2015 but single figure days (third of April) are stored as 2015/04/ 3 with a space before the three.

Excel ignores the space and processes it correctly as 03/04/2015 (European format) but access creates an error for each row that includes a space and doesn't import the row.

Any clues??

 

[plog]

You should import this to a temporary table, importing that date field as text. Then you create a function to convert that format to a date. Then you create an APPEND query to move the data from the temporary table to wherever this data needs to be actually stored, using that custom date conversion function on that field so that it goes into the actual table correctly as a date.

 

[Libre]

Plog is right.
To manipulate Date data and String data it's very useful to know the built in functions:
Left()
Right()
Mid()
Trim()

It's also real useful to know the datatype Variant. The Variant does not mind going from a date to a text string to a number and back again.

 

[Tieval]

Many thanks