In Access 2000, I importing a table by linking to an Excel file. When I do this the numeric data is coming over as text and I get a num # error in the cells which are supposed to be numeric. In the Excel spreadsheet, the number cellls are set to number with 0 decimal places. In Access, I checked the table in design view and the numeric fields were set to text. I tried to change the fields to numeric but it would not save as numeric. How can I link the excel file so that the fields are numeric?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Linked File not Numeric Data
- Thread starter RWALTZ
- Start date
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
One of the reasons for the strange import behaviour is that Access will usually guess the data type by looking at the first few rows in the field. If they are blank it will default to Text.
When you import the data, you get the option to set the data type at the third or fourth step in the Wizard. Make sure that the data type is set to Number and try again.
If that doesn't work you can import into a new table, then use an Append query to add the records to the real table. At that stage you can convert the data to numbers by using an expression like CLng([My Field])
Denis
When you import the data, you get the option to set the data type at the third or fourth step in the Wizard. Make sure that the data type is set to Number and try again.
If that doesn't work you can import into a new table, then use an Append query to add the records to the real table. At that stage you can convert the data to numbers by using an expression like CLng([My Field])
Denis
Upvote
0
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
I tend not to use linked Excel files, for three reasons. They are:
1. Performance; native Access tables are faster.
2. Locking; if you try to open the workbook while the Access DB is open, or open the DB while the workbook is open, you will get an error message.
3. Relevant for later versions of Access; linked Excel tables are read-only.
However, look at the spreadsheet. Are there any blank rows before the data starts in the problem fields? If so, reorganise the data (or include a dummy record) so Access gets the data type correct.
Denis
1. Performance; native Access tables are faster.
2. Locking; if you try to open the workbook while the Access DB is open, or open the DB while the workbook is open, you will get an error message.
3. Relevant for later versions of Access; linked Excel tables are read-only.
However, look at the spreadsheet. Are there any blank rows before the data starts in the problem fields? If so, reorganise the data (or include a dummy record) so Access gets the data type correct.
Denis
Upvote
0
Similar threads
- Question
