Linking Excel File to Access (XP)

Topic

New Reply

I’m trying to link an Excel file .xls (exported from a custom app) so that I can manipulate the data and write Access reports. Two fields are giving me fits. These fields hold some alphanumeric entries and some straight number entries. My problem is the reverse of what you’d expect. Access sets the data type as text and the alphanumeric entries come through correctly. The numbers (integers) come through as #Num!. This is school information – and the grade field displays KA and KP (for morning and afternoon kindergarten) but not 1, 2, 3, etc.

My original export from the application can be saved as a .csv file, but doing this loses date fields.

Your help and explanations will be appreciated.
Thanks

Reply | Quote

Replies

That sounds very strange – why would Access display #Num in a text field?

Does it work correctly if you import the Excel file instead of linking it?

If you like, could you post a small but representative sampe file (replace sensitive date with dummy data)?

Reply | Quote

I tried to send you a couple small files – a tiny sample Excel file and the Access file as well. I was unable to do it – maybe because of settings here at the office. If I can’t get them done, I’ll take them home and try again.

Reply | Quote

I tried to send you a couple small files – a tiny sample Excel file and the Access file as well. I was unable to do it – maybe because of settings here at the office. If I can’t get them done, I’ll take them home and try again.

Reply | Quote

The file was simply too large. I’ve zipped it and will try sending it that way. I look forward to your comments

Reply | Quote

The behavior is certainly strange. I con’t remember having seen #Num in a text field, but it does happen the way you described it. The only workaround I can think of is to create a table with the correct structure and to import the Excel data into this table. See attached demo.

Reply | Quote

The behavior is certainly strange. I con’t remember having seen #Num in a text field, but it does happen the way you described it. The only workaround I can think of is to create a table with the correct structure and to import the Excel data into this table. See attached demo.

Reply | Quote

Access has interpretted the Grade column as a double precision number field for some strange reason. Then when you have alpha characters in this type of field it will show as #num. Change the Grade column as a text field (Hans has already explained this).

Reply | Quote

Access has interpretted the Grade column as a double precision number field for some strange reason. Then when you have alpha characters in this type of field it will show as #num. Change the Grade column as a text field (Hans has already explained this).

Reply | Quote

The file was simply too large. I’ve zipped it and will try sending it that way. I look forward to your comments

Reply | Quote

That sounds very strange – why would Access display #Num in a text field?

Does it work correctly if you import the Excel file instead of linking it?

If you like, could you post a small but representative sampe file (replace sensitive date with dummy data)?

Reply | Quote