Import to Table Format Question (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans. That worked perfectly.

Reply | Quote