Type conversion failure in Microsoft Access 2007

Question

I am importing a HTML file into a database in Microsoft Access and the HTML file contains a column with date.

For instance, the column contains data like "25 May 15". When I import the data into a table, I face two Import Errors.

First Error:

It gives an Import Error called Type Conversion Failure when I import the data as Date/Time. However, it works fine when I import it as text format.

Second Error:

When i add similar table which the date data is of "24 May 15", on top of the current table. It gives an Import Error called Type Conversion Failure. It doesn't allow me state the type of format of the date column.

Need some guidance on how to solve this date/time issue.

It sounds like Gustav's solution is your only option, unless you want to write a script to remove the double quotes from your HTML before importing. — Mark C.
– Mark C., Commented May 26, 2015 at 19:43

Gustav · Accepted Answer · 2015-05-26 11:39:20Z

Import the date as text to a (temporary) table.

Then use a query to convert the text date to a true date with DateValue(). It will convert, say, the string "25 May 15" to the date value 2015-05-25.

Siddd · Accepted Answer · 2015-06-05 10:28:15Z

You can use the "Advanced" button while importing to change how Access checks the date from your source file. Set the delimiter as " " and check if your date format is correctly set to "DMY" and "4 digit year" is unchecked. Hope this solves the issue, did so for me.

Gm709 · Accepted Answer · 2016-11-14 17:59:07Z

Also had the problem with csv file import. My work around invoked setting the date delimiter as a hyphen instead of forward slash. I notice this discrepancy whilst testing imports via setting the field type as text.

Collectives™ on Stack Overflow

Type conversion failure in Microsoft Access 2007

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related