How can I Inner Join two tables in Access when one column is a text and the other is number?

Question

I have a program in access that is using some linked ODBC tables. I had originally had a query that contained the following INNER JOIN:

FROM Neptune_FN_Analysis 
INNER JOIN Neptune_prem ON Neptune_FN_Analysis.PremID = Neptune_prem.premice_id 

This worked just fine until the column Neptune_prem.premice_id got changed from a number to a text data type. So now I need a way to use an INNER JOIN on two columns when one is a number and the other is a text.

Please keep in mind that I am not allowed to simply change the data type in the table that the linked ODBC tables are looking at.

If this is impossible or a rediculous amount of code my other logical option would be to make a query to make a local table that I can edit with all of the same data in the Neptune_FN_Analysis table and in the code after that query edit the column I am joining with to type text. I would prefer to just modify this one SQL query if it is reasonable though.

Answer 1

Have you tried using CAST('abc' AS varchar(5)) the numeric column in varchar?

EDITED

You should use clng to cast the text as a number...

Answer 2

If you're talking about turning "500" into 500, check out Val, CDbl, CInt, CCur, CDec, and other conversion functions:

FROM Neptune_FN_Analysis 
INNER JOIN Neptune_prem ON Neptune_FN_Analysis.PremID = CInt(Neptune_prem.premice_id)

Answer 3

First I'd track down who made the change and find out why. You may have bigger probklems than just getting numbers to match. These changes aren;t made for no reason, if they changed form a numeric filed to atext filed, likely it is becasue they need to put text data into the field and now you won't be able to compare at all if you continue to use numerics. Database changes need to consider what else might be affected and this one clearly didn't. Find out who did it and why as the first step.