Access linked excel table data format issue

Question

In Access 2013 I have a linked table where one field is a date and displays as a date in Excel.

However, in Access it displays as a 5 digit number stored as Short Text.

In some records, this field will be blank.

I've tried several methods within a query to convert this into a date.

      Using the format field on the property sheet.

      Using CDate - I get: "Data type mismatch in criteria expression"

      Using FormatDateTime function

     If I copy the data from Excel into a new table in Access, it will copy as a date.  I really don't want to copy each time.

In Access 2013, how do I convert this back into a date?

Answer 1

Hi Mark, I will try to help.

First, you need to understand how Access stored Date/Time values. They are stored as a double precision value where the Integer portion represents the number of days since 12/31/1899 and the decimal portion is a fraction of day so that .25 represents 6AM.

So the number you are seeing is Access converting the date into its own storage. That's why you can't convert it using CDate. What you need to do is format the display of the value so it displays as a date. So when you display this value in a control on a form or report you can set the format to Short Date and it will display as a date. You should be able to do this for the column in a query as well.

If you need any further clarification on this feel free to ask.