2

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!

HansUp
  • 95,961
  • 11
  • 77
  • 135
Ian
  • 23
  • 1
  • 3

1 Answers1

1

"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
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • i understand that the number relates to the correctly formatted date value, but how do i then get them displayed correctly in the report. The field is called 'Start Date' – Ian Jul 19 '13 at 20:43
  • Use a query as the report's record source. In the query, you can transform the number as you wish. – HansUp Jul 19 '13 at 20:44
  • ahh so the format is set in the query and not the report. I will give it a try! – Ian Jul 19 '13 at 20:48
  • worked a treat!! many thanks!! I was trying to get it to work in the report and not the query :) – Ian Jul 19 '13 at 20:55
  • Right, I revised the answer to make it clearer I was talking about a query. You could also apply those functions to data controls in a report, but I think it's more convenient to do that in the report's record source query. – HansUp Jul 19 '13 at 21:05