[AccessD] Access 2K to Excel changes field format

Hale, Jim Jim.Hale at FleetPride.com
Thu Mar 9 16:57:42 CST 2006


I'm going to guess that D0 makes Excel think it is "0 decimals" . Try adding
a space in front of the string- that may help although it is not very
elegant
Jim Hale.

-----Original Message-----
From: O'Connor, Patricia (OTDA)
[mailto:Patricia.O'Connor at otda.state.ny.us]
Sent: Thursday, March 09, 2006 4:36 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access 2K to Excel changes field format


The query is used to put data into an Access report. The single quote
shows up both in the Access report and the excel cell. That does not
look good nor does it make sense to user seeing this.  If I try to get
rid of the single quote in the Access report then it gets reformatted in
excel when using the Analyze in Excel. The field is specified and stored
as text in both access and oracle and specified text in Excel.  

Here are some examples.  When I use the original query in Access that
creates the report directly in excel it did not mess up the ID.    
66005769905A01                  66002730270C01   
66006665460J01                  66001747189H01
66007974120D01              66001215257F01

If  while looking at the access report and I click Analyze with Excel it
does change the ID.
66005769905A01           66002730270C01
66006665460J01            66001747189H01
6.6008E+11                66001215257F01


Why would it mess up only certain id's and why when creating from Access
report? Is there a way to tell if whether the wrong  version of the
analyze option is being used.  I do have Access 97 and 2K on this
machine.  

Thanks

**************************************************
* Patricia O'Connor
* Associate Computer Programmer Analyst
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at otda.state.ny.us
**************************************************
 

> 
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or
otherwise legally protected. It is intended only for the addressee. If you
received this e-mail in error or from someone who was not authorized to send
it to you, do not disseminate, copy or otherwise use this e-mail or its
attachments.  Please notify the sender immediately by reply e-mail and
delete the e-mail from your system. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Charlotte Foust
> Sent: Thursday, March 09, 2006 03:47 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access 2K to Excel changes field format
> 
> In the query, concatenate a single quote to the front of the value.
> Excel will see that as text and leave it alone.
> 
> Charlotte Foust
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> O'Connor, Patricia (OTDA)
> Sent: Thursday, March 09, 2006 12:21 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Access 2K to Excel changes field format
> 
> 
> Hi all
> 
> I have an ID field which is TEXT 14 characters
> 
> In the Access report it is being displayed correctly 66007573735D01
> 
> When this report is put to EXCEL through the "Analyze it 
> through EXCEL"
> it is dropping the D so the ID changes to 6.60076E+11  I have 
> changed the excel format to TEXT. If I then go to that cell 
> highlight and leave it will look like this 660075737350
> 
> I have the field formatted in the query and access report as 
> text @@@@@@@@@@@@@@. Is there something in excel or access 
> that I have to change in the options. This does not make 
> sense and it is causing problems sending the results to others.
> 
> Thanks
> 
> **************************************************
> * Patricia O'Connor
> * Associate Computer Programmer Analyst
> * OTDA - BDMA
> * (W) mailto:Patricia.O'Connor at otda.state.ny.us
> * (w) mailto:aa1160 at otda.state.ny.us
> **************************************************
> --------------------------------------------------------
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.


More information about the AccessD mailing list