Date field problem with Link to CSV

Question

I am linking to a CSV file. One of the fields is a date. I select Date/Time as the field type in Access. However, when I open the table, I get "#Num!" as a value for all records. What am I doing wrong?

Answer 1

CSV files are merely text, and Access does have trouble interpreting date/time values and other fields that have symbols (such as currency or percent.)

The best solution might be to import that file into a table that has only Text type fields. Then you can create a query to massage the values so they get interpreted correctly and outputs them as valid dates, true currency, or valid fractional (percent) values. You can then use an Append query to write the data  to a table that has the appropriate data types.

Not sure if I've run into the dates problem more frequently than others as a result of living in a country that uses a non-US date format, but it certainly is an issue.

---

Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to forum, rather than allenbrowne at mvps dot org.

Answer 2

Is the date text in a format that Access recognizes?

---

Build a little, test a little.