Fields with text & numbers in linked tables

[jaaret]

I have an Excel workbook linked to my database that contains questions and answers to a quiz. The answers are a mix of text and numbers. I've formatted the answers as Text in Excel. However, in the linked table the number answers appear as '#Num!' (the data type for the field is Text).
My workaround has been to manually add an apostrophe before the numeric answers in Excel. This works but takes time since the quiz data is continually being updated. Is there a better way to approach this?

 

[dhookom]

better approach"? How about not using Excel and keep everything in Access.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jaaret]

The Excel files are exports from an online survey tool. The survey tool only exports to .txt, .csv, .xls, etc. When I export the newest survey data, I overwrite the Excel files that are linked to Access. I've set up Access to import the new data from the Excel exports into an Access table.

Please review original question now that I've provided better context.

 

[JerryKlmns]

jaaret

I would go for .txt exported files, create an import specification where you can define exactly what data type resides in a field and import that way.
If you don't, then try to sort your data in a way that the first excel row (to be an access record) contains text in the excel columns (access fields) containing text+numbers.