Two problems with Excel in Access

[ReluctantDataGuy]

First problem: Linking an Excel 2002 spreadsheet into Access 2002, I find that Access displays #Num! in fields that contain numbers only, but displays correctly when the cell contains alpha-numberic values.

Interestingly, this only occurs with a link. Importing the Excel file results in correct data being displayed instead of #num!

Second problem. Giving up on linking, I went with an import function. 4 of the Excel columns are defined as Date fields. All 4 fields are defined as Text in Access (because the Excel file has non date values in the date columns - and you know how Excel users love to define a data type for a column and then enter data that doesn't match that data type - and Excel allows it... ;<(

The problem is that while 1 of the 4 fields in Access displays the non date fields correctly, the other 3 of those fields are blank.

Interestingly only 1 of the 3 fields that do not display the data correctly actually creates an import_errors table. The other 2 simply drop the non date values, leaving a blank field (not sure if its "" or Null).

FYI: I've checked all the data types in Excel and Access carefully, and other than the Excel Date --> Access Text difference, the only other difference I see is that the one field that displays correctly has a size of 255 characters and the others are 50. This is how it was created when I imported the Excel table. I've tried manipulating the text length and it doesn't seem to have any effect.

I've seen other threads in TekTips dealing with #Num! but they relate to earlier versions of Excel and Access.

Color me stuck.

Thanks in advance for any help I may get.
ReluctantDataGuy

 

[ineuw]

Unfortunately, the safest way to import Excel data into Access is as text. The export/import process tries to import (guesses) the incorrect data by enclosing it with double quotes, for example: A text field in Excel containing: 123 Maple Ave, will be imported as "123 maple Ave," because of the comma at the end.

It will also reject commas, $ and characters other than a period in a numeric/currency field, and this also applies to dates which are incorrectly typed in Excel.

You should find a way to restrict the data entry in Excel to the correct data type and make sure that the Excel cell data type matches the Access field data type.

To test the file before import into Access, save the Excel file As Tab delimited and then search the text file for "", commas, $ etc. in the relevant column. This should be your guide in designing the proper restrictions in Excel.

 

[AvGuy]

The Microsoft ISAM that converts Excel data to Access data has a pecularity that will kill you until you figure it out. Excel generally uses a variant data type for all entries while Access requires strict data typing. The ISAM looks at the first entry in the Excel table column and types the entire column to match that entry. Thus, if your first entry is SHAZAM the column is text, but if the first entry is 12345 then the column is typed as numeric. Be careful of hyphenated numbers such as 12345-12 because it will be typed as numeric since 12345 - 12 = 12333. As mentioned above the safest way to import is as text and a numeric value can be converted to text by enclosing it in quote marks.