Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered: Data changes when I export from Access report to Excel Spreadsheet

    I have two completely separate databases running. On both databases I have the same persons SSN entered. Both databases have the correct SSN on the table and forms and reports. Hwever, when I export the data from a report to excel the SSN changes to a -476460.

    This problem is only affecting certain entries. Not all. Also, this problem just arrived when my company upgraded to Office 2003 from Office 2000.

    I have checked excel settings, I have converted both databases to 2003 format. I have also tried rebuilding reports. Nothing has worked.

    Any ideas???

    Thanks

    Koop

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    What is the data type for SSN? Should be text.

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    an another question

    whay are you exporting data to excel - can you undertake the function in access anyway - saves time if you can
    gareth

  4. #4
    Join Date
    Sep 2006
    Posts
    3
    the data field is a text field and formated for a SSN. Unfortunately, I have to be able to export this info to excel due to other agencies need to be able to further manipulate the data.

    Like I said, this is wierd. It worked until I went to Office 2003.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can try formatting it as specifically text (make it a string) in the query (make a query). I'm not sure if that would work but it might be worth a try. Either that or maybe set the format in the report on that specific field.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Excel will change text to numbers given half a chance. One option is don't open excel after you have created the file. Another is to prefix the values that could become numbers with a ' to force Excel to treat these values as text.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Pootle's right that excel likes to treat it as numbers and is probably doing some kind of equation on those fields. Pootle, are you refering to a ' in an expression in a query or in excel sheet? I was thinking of them exporting a query with an expression like - SSNDump: str([SSN])) or possibly even a format command in a column in the query.
    Last edited by pkstormy; 09-30-06 at 21:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •