i have an excel sheet linked into access which has varying data type. I'm having an issue specifically with dates though. In excel dates are formated as dd/mm/yyyy. When the data reaches access is it numeric. i.e. the number that relates to the date ... 19/07/2013 is being brought through as 41474.
I have tried to change the access table format, but as its linked the format cannot be specified on the access side of things.
Is there a way i can ensure that the date format is maintained?
Alternatively, is there a way i can manipulate the 'number' into a date format so that when reports are generated by users on the data, the report would show the date format correctly?
any advice greatfuly received!
"Under the covers", the Date\Time data type is actually double precision float. So you can use query with functions to transform those numbers from the linked table as you wish --- to explicit Date/Time values or formatted strings containing the date in your preferred format..
Here's a couple of samples from the Immediate window.
? CDate(41474)
7/19/2013
The date was displayed in my (American) locale setting. If you want it as a string in "dd-mm-yyyy" format, you can use the Format() function. (It's still the same Date/Time value, but just displayed in a different format.)
? Format(CDate(41474), "dd/mm/yyyy")
19/07/2013