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

[D]

Re: Exporting Years to Excel Problem

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" <darkohrgovic@h otmail.com> wrote in message
news:3sTsb.4689 5$Ws6.31817@edt nps84...[color=blue]
> 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.[/color]
Only[color=blue]
> 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[/color]
format[color=blue]
> 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
>
>
>
>[/color]

[Ilan Sebba]

Re: Exporting Years to Excel Problem

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" <darkohrgovic@h otmail.com> wrote in message
news:fXTsb.4741 3$Ws6.21598@edt nps84...[color=blue]
> 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" <darkohrgovic@h otmail.com> wrote in message
> news:3sTsb.4689 5$Ws6.31817@edt nps84...[color=green]
> > 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.[/color]
> Only[color=green]
> > one problem: one of the fields is a "SchoolYear " TEXT field that[/color][/color]
contains[color=blue][color=green]
> > data such as 2000/01, 2001/02, etc. If I export a Query with this kind[/color][/color]
of[color=blue][color=green]
> > 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[/color][/color]
36526,[color=blue][color=green]
> > 36923, etc, being the days since 1900 based on the initial year portion[/color][/color]
of[color=blue][color=green]
> > the text field (and when I format the cell in Excel as Date with the[/color]
> format[color=green]
> > yyyy, I get only the 2000 or 2001, etc).
> >
> > Anyone know why excel converts report text data, but not query text[/color][/color]
data?[color=blue][color=green]
> > Solutions?
> >
> > Thanks in advance for ideas,
> >
> > Darko
> >
> >
> >
> >[/color]
>
>[/color]

[D]

Re: Exporting Years to Excel Problem

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 decoy@yahoo.com>
wrote in message news:newscache$ 48fboh$oh5$1@ln ews.actcom.co.i l...[color=blue]
> 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[/color]
cannot[color=blue]
> convert it.
>
>
> "D" <darkohrgovic@h otmail.com> wrote in message
> news:fXTsb.4741 3$Ws6.21598@edt nps84...[color=green]
> > In case anyone runs into this problem in the near future.
> >
> > By changing the data from 2001/02 to 2001/2002, Access no longer[/color][/color]
converts[color=blue][color=green]
> > the data when exporting to Excel. Don't ask me why, it just doesn't.
> >
> > D
> >
> >
> > "D" <darkohrgovic@h otmail.com> wrote in message
> > news:3sTsb.4689 5$Ws6.31817@edt nps84...[color=darkred]
> > > I've created a report with many subreports of aggregate data. I want[/color][/color][/color]
my[color=blue][color=green][color=darkred]
> > > client to be able to export this data to Excel to make her charts,[/color][/color][/color]
etc.[color=blue][color=green]
> > Only[color=darkred]
> > > one problem: one of the fields is a "SchoolYear " TEXT field that[/color][/color]
> contains[color=green][color=darkred]
> > > data such as 2000/01, 2001/02, etc. If I export a Query with this kind[/color][/color]
> of[color=green][color=darkred]
> > > data to Excel, it gives me the text value of this field; however, when[/color][/color][/color]
I[color=blue][color=green][color=darkred]
> > > export a Report bound to this TEXT field, Excel gives me the values[/color][/color]
> 36526,[color=green][color=darkred]
> > > 36923, etc, being the days since 1900 based on the initial year[/color][/color][/color]
portion[color=blue]
> of[color=green][color=darkred]
> > > the text field (and when I format the cell in Excel as Date with the[/color]
> > format[color=darkred]
> > > yyyy, I get only the 2000 or 2001, etc).
> > >
> > > Anyone know why excel converts report text data, but not query text[/color][/color]
> data?[color=green][color=darkred]
> > > Solutions?
> > >
> > > Thanks in advance for ideas,
> > >
> > > Darko
> > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[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).