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 4 3917
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
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
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
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).
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: sridevi |
last post by:
Hello
How to export data from ms-access database to excel worksheet using ASP.
mainly i need to export data to multiple worksheets. it is very urgent to
us.
i have a sample code which works...
|
by: Kenneth |
last post by:
How do I remove the limitation in Access that deny me from exporting 24000
rows and 17 columns (in a query) into Excel?
Kenneth
|
by: Andi Plotsky |
last post by:
I have an Access app (running under Access2000) where I want to export the
data to an Excel spreadsheet. This has worked fine in the past, but after
taking it to my client site (where I ran it...
|
by: Regnab |
last post by:
I've got my code working so that it'll count the number of columns in
the table and move across (eg Range A-P and then range Q-W). Problem is
when I get to the end of the single letters and get...
|
by: Suffrinmick |
last post by:
Hello Everyone
I've built a database using Access 2000 which includes a query which is
built using a form containing filters. No problem. When I export the
results of the query to excel, (File >...
|
by: pmud |
last post by:
Hi,
I am exporting data from an EDITABLE DATA GRID EXCEL. But the 1st column
in data grid is Edit Column. I want to display all columns in Excel except
for the Edit column.
The following...
|
by: Mustufa Baig |
last post by:
I have an ASP.NET website where I am showing off crystal reports to users by
exporting them to pdf format. Following is the code:
----------------
1 Private Sub ExportReport()
2 Dim oStream...
|
by: Snozz |
last post by:
The short of it:
If you needed to import a CSV file of a certain structure on a regular
basis(say 32 csv files, each to one a table in 32 databases), what
would be your first instinct on how to...
|
by: jpatchak |
last post by:
Hi, I have kind of a strange problem. I have a report that I need to export to excel. The report opens fine in Access and when I export it or click on "Analyze It with Microsoft Office Excel," I...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |