Data changes when exported to excel (1 Viewer)

jomuir

Registered User.
Local time
Today, 22:57
Joined
Feb 13, 2007
Messages
154
When I export a report with a text field (financial date) to excel the data is changed!!!

2005/06 changes to 38504
2004/05 changes to 38108

I presumed it is dividing the values but the arithmetic does not add up – the field in excel is a general field so I have no idea why it is changing.

Any ideas how I can get this to export the actual data? The only way I have found so far it using | (pipe) i.e. 2005|06 however this is the delimiter that I have to use for importing data in the first place!!!!

Any ideas?
 

Rabbie

Super Moderator
Local time
Today, 22:57
Joined
Jul 10, 2007
Messages
5,906
The data in Access is being exported in date format to Excel. 38504 is the way that Excel stores 1st June 2005. How is the data held in your Access DB. If it is held as text field it should then export correctly to a text format in Excel.
 

jomuir

Registered User.
Local time
Today, 22:57
Joined
Feb 13, 2007
Messages
154
38504 is the way that Excel stores 1st June 2005.
No idea how Excel gets that number from the date, makes no sense to me, but atleast it does to someone :)

How is the data held in your Access DB. If it is held as text field it should then export correctly to a text format in Excel.
This is a text field in my table - used as a data source for my search form and it is from here that I execute my report....so I still believe it to be in text format as I have not made any chages to the data, actually the data is locked so it cannot be changed.
 

Rabbie

Super Moderator
Local time
Today, 22:57
Joined
Jul 10, 2007
Messages
5,906
Both Access and Excel store dates as the number of days since 1/1/1900 so thats how it gets the date.

It looks like Excel is guessing it's in Date format because it sees the / . Thats why its giving you the wrong result. And why it worked when you used | instead.
 

Users who are viewing this thread

Top Bottom