Execute CStr() expression inside JOIN statement

Question

I have two fields in separate linked tables with the same data, but different data types. I can't change the datatypes in the tables. I am trying a query that joins the two tables together based on this data, but since the types are different I need to convert the number to a string. What I have been trying, basically, is this:

...
FROM Table1 LEFT JOIN Table2 ON CStr([Table1].[Column]) = Table2.Column
...

I just keep getting error messages, mostly "Join expression not supported."

Can anyone shed some light on what I may be doing wrong/what I could do better?

Thank you.

Answer 1

Here is your FROM clause reformatted:

FROM
    Table1.Column
    LEFT JOIN Table2.Column
    ON CStr([Table1].[Column]) = Table2.Column

Notice it uses Table1.Column and Table2.Column as the data sources. And those are columns (fields), not tables (real or virtual).

Try it this way instead:

FROM
    Table1
    LEFT JOIN Table2
    ON CStr([Table1].[Column]) = Table2.Column

Access' query designer has trouble dealing with with JOINs which include functions in the ON expression. Although I don't see evidence that is confusing the issue in your question, I'll suggest you rule out that possibility by pasting the following statement into the Immediate window and executing it there.

Set rs = CurrentDb.OpenRecordset( _
"SELECT Count(*) AS row_count" & vbCrLf & _
"FROM Table1 AS t1" & vbCrLf & _
"LEFT JOIN Table2 AS t2" & vbCrLf & _
"ON CStr(t1.[Column])=t2.[Column];") : _
? rs(0) : _
rs.Close : _
Set rs = Nothing

Notice each of those line continuation characters (underscore, "_") must be preceded by a space and have no characters after.