Results 1 to 3 of 3
  1. #1
    crcastilla is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2011
    Posts
    18

    Question

    This may be an Excel question but I'm not sure.

    I have linked a Excel spreadsheet into Access. The thing that they are numbers and when numbers like "00212" or "03309" are put into the access table the zero at the begging do not appear (so they are like this; "212" and "3309").

    I tried to go to change something me maybe on the data type or something but it says i can not make any changes unless in the source data which is in Excel.



    Is there something I could do?

    Thank you in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I tested a spreadsheet link with columns set to text and custom format. The leading zeros were retained by the link. If your spreadsheet is showing the leading zeros then the cells must already have a text or custom format setting. I don't know why the link is dropping the leading zeros. Unless Access2010 handles that better than 2007.

    Don't understand some of what you describe. You state 'when numbers like "00212" or "03309" are put into the access table' - do you mean values in the spreadsheet have leading zeros?
    Last edited by June7; 03-24-2012 at 01:48 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    I usually 1st try to set the entire cell column as a text format. If that fails, I may create a 'dummy' row under the header row and put in 1 alpha character instead of numbers in those columns (since the import routine tends to always use the 1st row of data to determine the data type correctly even though this sometimes incorrectly interprets numbers as integers versus strings.) This row would get deleted after import. Not the best way but it works.
    If that doesn't work, you can create an Import File Specification. I hate to do this since it limits the flexibility of new data and must be maintained with strict number of characters within the columns but it'll let you fine-tune what fields and the types of data. You would then use the File Specification Name as a parameter in your docmd.transfer.... command.

    If it's a file you get regularly from some kind of export routine, I'd ask if they could surround this data with a double-quotes around the value to force it to see it as a string.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums