[AccessD] Access 2K to Excel changes field format

Patricia,

    It is observed that if all characters preceding the letter D in a text string are numeric digits, Excel interprets D as a mathematical qualifier, leading to the problem faced by you. Placing a space as first character in this string does not work. On the other hand, single quote at start of the string does reproduce the contents faithfully, but the single quote remains visible in Access report as well as in Excel, a situation you find not quite ideal.

    It is found that if the stream of numeric digits preceding D is interrupted by placing a space, hyphen or underscore just before D, the string gets reproduced faithfully in Excel. Typical samples:
    (a) 66007974120 D01
    (b) 66007974120-D01
    (c) 66007974120_D01

    If your situation permits adoption of any of the revised patterns (a to c) above, it would give you proper match between the Access report  and Excel output.

Best wishes,
A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: O'Connor, Patricia (OTDA) 
  To: Access Developers discussion and problem solving 
  Sent: Friday, March 10, 2006 04:05
  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
  **************************************************
   

  -----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
  > **************************************************