Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import to Table Format Question (2000)

    I'm importing data from a spreadsheet into a table in Access and having trouble with the format of some of the data in one of the fields after it's been imported. The field I'm having trouble with is a referenece number from an external source, which can vary in length, but will not be longer than 16 characters, and content, i.e. some references will contain only numeric values, while others can contain both alpha and numeric characters (additionally, leading zeros will be retained). For example, this reference number is imported correctly: 002KID0501040899.

    In Excel the cell format is set to Number, zero decimal places, no commas. In Access, the field is set to Text. When a reference number is all numbers and exceeds 11 digits, e.g. 338300400460000, it returns, e.g. 3.383E+14 in Access after it has been imported (and in Excel as well when I change the format from Number to Text).

    If I set the format in Access to Number (from Text), only the records where the reference number only contains numeric values, i.e. no alpha characters, are imported.

    Any suggestions how I might be able to import all references numbers correctly?

    Thanks,

    Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import to Table Format Question (2000)

    You should set the format of the column in Excel to Text before entering the data, or if the data have already been entered, you should update the values after changing the format to Text (for example by editing the values without changing anything). If there are a lot of values, this could be done through a macro - post back if you need assistance with that.

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import to Table Format Question (2000)

    Thanks Hans. That worked perfectly.

Posting Permissions

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