VBA left join cannot combine object. (error code: 0x80040e21)

I'm confusing with the SQL query via VBA ADODB connection. Please help to figure out what I miss in the query...

I have two tables, TABLE_A and TABLE_B, and both of them have ID and CODE columns contains data as following:

A_ID contains data like, 'abcdefgh';
B_ID contains data like, 'ijabcdefgh';
A_CODE contains data like 'klmstuij' or 'kl-mstuij';
B_CODE contains data like 'klmstu'.

Then I have a query like this,

strSQL = "SELECT * " & _ 
"FROM TABLE_A a " & _ 
"LEFT JOIN TABLE_B b ON ((a.[A_ID]=RIGHT(b.[B_ID],8)) AND (LEFT(REPLACE(a.[A_CODE],'-',''),6)=b.[B_CODE]))"

And I got an error when execute this query, which says:

Runtime error'-2147217887(80040e21)', in Memo, OLE or Hyperlink object

((a.[A_ID]=RIGHT(b.[B_ID],8)) AND (LEFT(REPLACE(a.[A_CODE],'-',''),6)=b.[B_CODE])) cannot combine.

I've tried query with one condition in LEFT JOIN and it works, the query like below:

strSQL = "SELECT * " & _ 
"FROM TABLE_A a " & _ 
"LEFT JOIN TABLE_B b ON (a.[A_ID]=RIGHT(b.[B_ID],8))"

Then I tried to add one more condition in LEFT JOIN without string transaction, and it failed with same error:

strSQL = "SELECT * " & _ 
"FROM TABLE_A a " & _ 
"LEFT JOIN TABLE_B b ON ((a.[A_ID]=RIGHT(b.[B_ID],8)) AND (a.[A_CODE]=b.[B_CODE]))"

Do I miss anything in the query?

Try

strSQL = "SELECT * " & _
"FROM TABLE_A as a " & _
"LEFT JOIN TABLE_B as b ON inStr(a.[A_ID], b.[B_ID]) or inStr( b.[B_ID], a.[A_ID] ) "