Exporting Years to Excel Problem

D

I've created a report with many subreports of aggregate data. I want my
client to be able to export this data to Excel to make her charts, etc. Only
one problem: one of the fields is a "SchoolYear" TEXT field that contains
data such as 2000/01, 2001/02, etc. If I export a Query with this kind of
data to Excel, it gives me the text value of this field; however, when I
export a Report bound to this TEXT field, Excel gives me the values 36526,
36923, etc, being the days since 1900 based on the initial year portion of
the text field (and when I format the cell in Excel as Date with the format
yyyy, I get only the 2000 or 2001, etc).

Anyone know why excel converts report text data, but not query text data?
Solutions?

Thanks in advance for ideas,

Darko

Nov 12 '05 #1

D

In case anyone runs into this problem in the near future.

By changing the data from 2001/02 to 2001/2002, Access no longer converts
the data when exporting to Excel. Don't ask me why, it just doesn't.

D
"D" <da**********@hotmail.com> wrote in message
news:3sTsb.46895$Ws6.31817@edtnps84...

I've created a report with many subreports of aggregate data. I want my
client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear" TEXT field that contains
data such as 2000/01, 2001/02, etc. If I export a Query with this kind of
data to Excel, it gives me the text value of this field; however, when I
export a Report bound to this TEXT field, Excel gives me the values 36526,
36923, etc, being the days since 1900 based on the initial year portion of
the text field (and when I format the cell in Excel as Date with the format yyyy, I get only the 2000 or 2001, etc).

Anyone know why excel converts report text data, but not query text data?
Solutions?

Thanks in advance for ideas,

Darko

Nov 12 '05 #2

Ilan Sebba

I think I know why:

"2000/01" - this can convert into a date value - January 2000, so Access
converts it.

"2000/2001" - this can no longer convert into a date value, so Access cannot
convert it.
"D" <da**********@hotmail.com> wrote in message
news:fXTsb.47413$Ws6.21598@edtnps84...

In case anyone runs into this problem in the near future.

By changing the data from 2001/02 to 2001/2002, Access no longer converts
the data when exporting to Excel. Don't ask me why, it just doesn't.

D
"D" <da**********@hotmail.com> wrote in message
news:3sTsb.46895$Ws6.31817@edtnps84...

I've created a report with many subreports of aggregate data. I want my
client to be able to export this data to Excel to make her charts, etc.

Only

one problem: one of the fields is a "SchoolYear" TEXT field that contains data such as 2000/01, 2001/02, etc. If I export a Query with this kind of data to Excel, it gives me the text value of this field; however, when I
export a Report bound to this TEXT field, Excel gives me the values 36526, 36923, etc, being the days since 1900 based on the initial year portion of the text field (and when I format the cell in Excel as Date with the

format

yyyy, I get only the 2000 or 2001, etc).

Anyone know why excel converts report text data, but not query text data? Solutions?

Thanks in advance for ideas,

Darko

Nov 12 '05 #3

D

Thanks, Ilan. I know that Access tries to convert when it can, so maybe it's
"one of those things" that had to be decided by the developers.

But why does it convert to an integer value representing the days since 1900
when it gets to Excel? If Access can convert it to a date, why doesn't it
show up as a formatted date in Excel? And why does Access only behave this
way with report, not query, export?

What's the best way to force Access NOT to convert between data types when
exporting, or is there one?

D

"Ilan Sebba" <ilan underscore sebba at btinternet stop com de***@yahoo.com>
wrote in message news:ne********************@lnews.actcom.co.il...

I think I know why:

"2000/01" - this can convert into a date value - January 2000, so Access
converts it.

"2000/2001" - this can no longer convert into a date value, so Access cannot convert it.
"D" <da**********@hotmail.com> wrote in message
news:fXTsb.47413$Ws6.21598@edtnps84...

In case anyone runs into this problem in the near future.

By changing the data from 2001/02 to 2001/2002, Access no longer converts

the data when exporting to Excel. Don't ask me why, it just doesn't.

D
"D" <da**********@hotmail.com> wrote in message
news:3sTsb.46895$Ws6.31817@edtnps84...

I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc.

Only

one problem: one of the fields is a "SchoolYear" TEXT field that

contains data such as 2000/01, 2001/02, etc. If I export a Query with this kind of data to Excel, it gives me the text value of this field; however, when

I export a Report bound to this TEXT field, Excel gives me the values

36526, 36923, etc, being the days since 1900 based on the initial year

portion of the text field (and when I format the cell in Excel as Date with the

format

yyyy, I get only the 2000 or 2001, etc).

Anyone know why excel converts report text data, but not query text data? Solutions?

Thanks in advance for ideas,

Darko

Nov 12 '05 #4

bendan

I have run into a similar problem. However I want my data formatted as a typical short date. Why is Excel formatting it as the number of days since 1900? If anyone knows how to get around this problem or disable this mangling of the data when it is exported please respond! It is very odd that this does not happen when the date comes from a control source (Query). It only happens when the date is set in the code (In the Detail_Format event in my case).

Jun 27 '06 #5