Query Join Between Two Different Data Types

[mpi]

I'm wondering if its at all possible to make a query that contains a join between two different data types (number and text). I cannot find a way. Thanks for the help...

 

[mpastore]

I believe you can do this so long as you cast one data type to the other in the join. Try something like:

select table1.*,table2.*;
from table1,table2;
where cstr(table1.numfld)=table2.stringfld

Mike Pastore

Hats off to (Roy) Harper

 

[mpi]

great! CStr is the function i was looking for. thank you so much Mike!!

 

[jrbarnett]

MPastore's method works well, but is obviously only a short term solution. The ideal method is obviously to change the data type in the table if at all possible to match.

You can then set a foreign key between the two if needed and that speeds queries up significantly.

John

 

[mpi]

It's not really possible with my system at work, we use an MRP(manufacturing resource planning) system called BAAN. Its tables are locked and the fields aren't changable. The reason I needed to do this was because a quote response letter program I'm writing in Access uses an autonumber field to generate quote numbers, and BAAN must store this in a reference field which is text.