'type conversion failure' import error (1 Viewer)

I have numerous excel sheets that contain number data that I am trying to import into an access database. The excel files were originally in text form but I was able to convert them to excel worksheets through a number of procedures. I have excel files for each month. Each excel file has hourly data. These data are numbers unless the data was missing, in which case a star('*', or '**') takes its place. I have code that automatically imports the excel sheets into an access table, each month being appended to one big table in access. The problem is that sometimes when there is a '*' or '**' an import error is generated as 'type conversion failure', but not always, some of the stars do get imported. My field types are set to memo (because I was experimenting and changed it from text). What is a possible source of this problem? I really would like to keep the stars as the single star and the double star have different meanings. I appreciate any help anyone might have. Thanks.

Now that you're moving from spreadsheets to relational tables, you need to change your thinking. Asterisks do not belong in numeric fields. You need an additional field to hold a code indicating the meaning of the single and double asterisks. That way you can import the data into a numeric field. Storing numeric data in text fields (or memo fields) will cause you numerous problems in the future.

It is NEVER correct to to use a single column to hold different types of data.

I am sure that you are correct about storing different types of data in the same field. However, I do not believe that I am doing such a thing. All of my data is the same, except for when there is missing data and an asteric appears. The reason that there are two different indicators is because a single asteric means instrument calibration while two indicate missing data for an unknown reason. I also am not worried about future problems resulting from storing my numbers as text. I am simply interested in importing all of my data as it appears in the text file (only converted to excel for ease of handling), but in one file rather than many monthly files.

For some reason these asterics are causing a problem in certain instance, but not in others. I have another database that also imports text files that use the same convention with the asterics, but without generating any import errors and successfully importing the asterics. I am trying to determine what the problem is with this new database, however largely unsuccessfully. I have changed from a Windows NT system to a Windows 2000 system and wonder if there was some change in how access is handling the transfer. Also, my older database doesn't use the single asteric, but '-' instead. Is the single asteric what is causing the problem? Sometimes, however, the single asteric is imported, so I am thinking it isn't the problem. Also, the asterics will were always imported before the 10th row, but sometimes after. Does this mean anything? If you, or anyone else, has any ideas I would greately appreciate some feedback. Thank you very much for you suggestion.

I have an update on my problem. It seems that if the first asterisk in an individual column occurs on or after the 10th row in my excel sheet then the import error is generated, and all subsequent asterisks in that column will also fail to convert. If, however, the first asterisk occurs before the 10th row (ie row 1 to 9) then all of the asterisks for that column in that month will be imported correctly. This seems odd to me since I have the field type in the table design in access set to text (or memo, have tried both). Does anyone have any ideas what the problem is? I appreciate help. Thanks.

The import wizard insists on using the spreadsheet's data types rather than the destination table's data types. Access is determining the field type by looking at the contents of the first 10 rows. If the data is numeric up to that point, it assumes that the column should be numeric and that's why you get errors for asterisks that appear in later rows. You can force Access to interpret the column as text if you edit the first numeric value and place a single quote in front of it.

Thanks, seems to have worked!