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 3847
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: Rina0 |
last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |