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

Question

I have an access program that uses some related ODBC tables. I originally had a query containing the following INNER JOIN:

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

This worked fine until the Neptune_prem.premice_id column changed from number to text. So now I need to use INNER JOIN for two columns if one of them is a number and the other is 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 eloquent code, my other logical option would be to make a request to create a local table, which I can edit with all the same data in the Neptune_FN_Analysis table and in the code after this request, edit the column with which I connect to enter text. I would prefer to just modify this SQL query if that makes sense.

Answer 1

If you are talking about converting “500” to 500, check 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 2

First, I will track down who made the changes and found out why. You may have bigger problems than just combining numbers. These changes were not accepted without any reason, if they changed the form with the numeric name filed in atext, this is probably due to the fact that they need to put text data in the field, and now you can not compare at all, if you continue use number. Changes to the database should take into account what else could be affected, and this was clearly not the case. Find out who did this and why as a first step.

Answer 3

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

EDITED

You must use clng to print text as a number ...