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.

In MS Access design view of the query design window is quite limited. Can you run straight from the SQL view window? — Fionnuala, Jun 21 '12 at 16:24
Then you have just run up against a limitation of the design window. You will have to use sql view to maintain your query. — Fionnuala, Jun 21 '12 at 16:29
It isn't a Design View issue that I'm having. The query will not run because it says that this join expression is not supported. — Tim, Jun 21 '12 at 16:31
On my PC I can run such a query from SQL view, for example `SELECT a.id, a.atext FROM table1 AS a INNER JOIN table1 AS b ON cstr(a.id)=cstr(b.id);` — Fionnuala, Jun 21 '12 at 16:33
The tables are enormous, so importing isn't an option. The tables are in the same Oracle database. — Tim, Jun 21 '12 at 18:36
If the tables are in an Oracle database, why are you not using a passthrough query? — Fionnuala, Jun 21 '12 at 19:04
I tried passthrough and ended up with the same result. Just a lot of 'Joint expression not supported' errors. — Tim, Jun 21 '12 at 19:07
Every search that I have done about this kind of join tells me that it's possible. I have even tested it myself on a different program. I just can't get Access to accept conversion functions inside the join, no matter if they're VBA- or Oracle-based. What function would you use for this situation in a passthrough if you were doing it? I feel like I've tried everything and every combination of everything. — Tim, Jun 21 '12 at 19:12
I do not use Oracle, but a passthrough in SQL Server might use Str, not CStr: `select a.* from table_1 a left join table_1 b on a.id=str(b.id)` — Fionnuala, Jun 21 '12 at 19:23
I reckon you need one of these http://psoug.org/reference/convert_func.html — Fionnuala, Jun 21 '12 at 19:26
Tried it again in a passthrough with the same error. Here is what I have: `FROM table1 LEFT JOIN table2 ON to_char(table1.col,'[mask]') = table2.col` Do you see anything wrong there? — Tim, Jun 21 '12 at 19:48
No, but I wouldn't. I am fairly sure that this is not an MS Access problem. It is an Oracle problem. You need to get the proper syntax for this kind of join in Oracle and use it in a passthrough query. I had exactly the problem you experienced with SQL Server and MS Access until I used syntax that SQL Server was happy with. — Fionnuala, Jun 21 '12 at 19:57
Here is the Oracle syntax that I just tested that works. As you can see from what I copied and pasted on my last comment, it is exactly what I'm running in Access: `FROM table1 LEFT JOIN table2 ON to_char(table1.col,'[mask]') = table2.col` — Tim, Jun 21 '12 at 20:07
Yeah it works just fine when I take that join out. Unfortunately that join is the backbone of the query. — Tim, Jun 21 '12 at 20:24
I have some ideas, but this is getting unwieldy and you do not have enough rep yet for chat. — Fionnuala, Jun 21 '12 at 20:34
Well thank you very much for all of your help and patience. I agree that this comment string is wayyyyy too long at this point. I'm just going to try my luck at getting a view created to link these two tables. There really is no reason why this shouldn't be working right now. I have another process that runs identical code on the exact same tables and it works just fine. I guess it's just one of those things. — Tim, Jun 21 '12 at 20:56
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12877/discussion-between-remou-and-tim) — Fionnuala, Jun 21 '12 at 21:12
Random idea -- did you try using a where clause instead of a join clause? — Joel Spolsky, Jun 22 '12 at 04:04
@JoelSpolsky - No, I didn't try that. How would I go about that? — Tim, Jun 22 '12 at 04:12
select blah blah from table1, table2 WHERE table1.x = table2.x — Joel Spolsky, Jun 22 '12 at 13:13

HansUp · Accepted Answer · 2012-06-23T20:43:46.543

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.

My apologies, my JOIN was not actually formatted that way. I mistakenly typed that in to the question. It was formatted the correct way - which you posted - the whole time and it did not work. — Tim, Jun 22 '12 at 15:42
I just updated my answer. I realize it's a long shot. However, if the statement returns a reasonable value without throwing an error, we need to change course. — HansUp, Jun 22 '12 at 16:36

Execute CStr() expression inside JOIN statement

1 Answers1