type mismatch in expression

[Guest]

I have a requirement to link a number of tables from difference sources -
text files, excel and access via linked tables. The 'key' reference is in
text format but sometimes the data in the linked table is in numeric format
which means the query fails with 'type mismatch in expression'.

I cannot go to the individual external files and convert the numeric data to
text (primarily access tables) because of the knock on implications.

Is there a way of converting the relationship to always work in text format?
The only way I have of resolving the problem at the moment is to import the
offending files and then convert the relevant field to type text, run the
link then delete the table. This takes longer and I am in danger of exceeding
the 2gb limit.

Many thanks in advance

 

[Van T. Dinh]

Not sure but try using the QBE to design your query and then switch to the
SQLView and change the link from something like:

.... ON T1.LongIDField = T2.TextIDField ...

to

.... ON T1.LongIDField = CLng(T2.TextIDField) ...

(don't switch back to DesignView, i.e. Grid View as Access will complain
....)

Alternatively, instead of using Joins, use the WHERE clause with suitable
data type conversion to specify the "link" you required.

 

[David F Cox]

this test worked for me:-

SELECT Tstates.state2, Table1.name1
FROM Tstates INNER JOIN Table1 ON val(Tstates.numastext) = Table1.ID;

as did:

SELECT Tstates.state2, Table1.name1
FROM Tstates INNER JOIN Table1 ON Tstates.numastext = trim(str(Table1.ID));

Neither query could be represented in design view. Both were generated from
a normal join (which would fail) and then editing the SQL.

HTH