How to join tables together on columns with different datatypes?

Question

A Microsoft Access implementation is throwing a type mismatch error while trying to execute a macro that opens up some queries. Most of the tables are linked to a SQL Server and I need to join two of the tables together that have different datatypes.

Table A:
REFERENCE TEXT

Table B:
REFNO NUMBER

I would ordinarily want to correct the issue on the SQL Server side, but there are multiple apps hitting the same database and it would take a considerable amount of time to test all of them. Furthermore, we are in the process of completely rewriting this application and any work I do today is completely throw-away...

If there is a way to make this join possible in access, I would save all kinds of time...

Answer 1

What is the datatype for each of the column, you mentioned?

If you want to compare it stringwise, you could do Cstr(myNumericColumn) = myStringColumn.
OR to compare it in numeric mode, do CLng(myStringColumn) = myNumericColumn.

Answer 2

Within Access you could use the CLng (or Cint) function to convert the Table A's REFERENCE values from text to number.

I would prefer to create a view of Table A in SQL Server to transform the field's data type before Access gets the data. You shouldn't need to test the view against your other existing apps. When your re-write make the view no longer useful, just discard it.

Answer 3

You can do the comparison in the criteria.

SELECT [REFERENCE], [REFNO]
FROM [Table a], [Table b]
WHERE [REFERENCE]=cstr(nz([REFNO],""))

You can also do a passthrough - a query in access that executes on the sql server and returns only the data.

SELECT [REFERENCE], [REFNO]
FROM [Table a], [Table b]
WHERE [REFERENCE]=cast([REFNO] as varchar(25))

HTH