Inner Join two tables when one column is a text and the other is number
join, ms-access, sql, vba
Solution
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)
Problem
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.