Inner Join two tables 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.

Solution 1

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)

Solution 2

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.

Solution 3

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...

Comments

[Nick Larsen]

I was going to say something like this, but he said he changed it to text which you should not be able to compare on.

[Bryan]

Yes, I did that before I even posted my problem on here. It was done not because text data needed to be put into the column. It was done because the source(s) they were importing data from didn't have requirements for that column to be a number field of any sort so they had it as a text. Therefore, to make sure there wasn't any problems importing the data, our field was changed to text as well. The values in that column are always supposed to be a number or blank. I wish the other companies requirements would have changed but I had no say in the matter.

[Bryan]

The Val seems to be working for now, the CInt didn't because apparently one of the numbers was too big. Anyway, Thanks.

[David-W-Fenton]

Val() is always preferred when you need to handle all numeric types.

[David-W-Fenton]

What a bunch of crazy idiots. The usual procedure would be to use a buffer table to process the data for non-comforming data and deal with it before it hits the database.

[David-W-Fenton]

Which version of Access/Jet/ACE added the CAST() function? (free clue -- read the tags before posting).

[user3001801]

I'm with David on this one. Solving a minor problem by creating a major one seems like a bad compromise.