Database-Excel-Database, Field Types...

[vanillapod]

I've got a small database that links to the workbooks in an excel file and uses them as linked tables. When i try and view reports the message "Type mismatch in join expression" is displayed. I guess this is happeniong because all fields are either linked as text or number and the query doesn't like it. The excel file is originally output from another database, is there any way of keeping the field types in the excel file when it is first output from the original database?

Cheers
Gary

 

[Sawatzky]

dear vanillapod,

couldn't you just convert them, when you fetch them from the excel.app ? i.e define a view on the linked table that converts the value to right type?

regards astrid

 

[BoxHead]

Gary,

If Astrid's solution works, I'd like to know more about it.

I've found that when Access imports data it 'guesses' at the datatype based on the first 25 or so values in a field. I've tried pre-defining the datatype in the excel sheet and the table that receives the data, but it's been overwritten each time.

A work-around could be to run a query of your table and use the

     Str([YourProblemField])

or

     Val([YourProblemField])

to convert the string to integer or vice versa. Base the reports on the query instead of the table.

HTH



John

Use what you have,
Learn what you can,
Create what you need.