Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't change data type using Excel import wizard (2000)

    I used to be able to change the data type of each field when importing a spreadsheet into Access 97. When I do it in (using import wizard) Access 2000, the data type pull down is GRAYED OUT. Unfortunately, an important column of the spreadsheet contains both text and numbers, and access has assigned it a 'double' as it's data type and I loose all the text records. I read one microsoft link that says this was fixed in office 2007, but I remember doing this a long time ago (as I said, with 97 I think).

    Thanks for any & all replies- Ralph

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

    Re: Can't change data type using Excel import wizard (2000)

    Yes, this worked correctly in Access 97, then it was messed up in later versions. You can try the following:
    - Import the Excel worksheet.
    - Delete all records from the imported table.
    - Open the imported table in design view.
    - Change the data type of the fields that have been imported incorrectly.
    - Close and save the table.
    - Import the worksheet again, but this time select the option to import into an existing table, and select the modified empty table as target.

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't change data type using Excel import wizard (2000)

    Hi Hans, Thanks for your reply.

    I got an error message importing into the empty table (after changing to the needed data type). I did find a way around it. Access looks at the first 25 records to determine data type (my text records were well down the list, which is why it called it a double). I inserted a dummy record into the first record of the spreadsheet. I put in a text value into the problem field and access finally give it a text format. I wish access would just format everything as text (& let you go back & change numbers/ dates ect). It would eliminate most import errors. Or better, restore the DT dropdown that works in 97...

Posting Permissions

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