Compare integer value of a text/string in OUTER JOIN ON clause (SQL in vba)

[lillyanna]

I would like to execute the SQL statement below in vba code written for MS Access, and I want the integer values of the ID fields to be compared in the JOIN ON criteria. However, I get an error when using CInt(). I've also tried Val(), CAST(), and Convert().
Set RecordSet = "SELECT A.id, B.id FROM A LEFT OUTER JOIN B ON CInt(A.id)=CInt(B.id)"

the entire id contains only numbers. For example A.id=0123, and B.id=00123 . I want to compare the value integer of the two so that A.id=B.id is true.

Thanks!
Lillyanna

 

[MSAccessRookie]

What error are you getting? I tried to imitate the Query and it worked as follows:

[/FONT]

[FONT=Fixedsys]Row Number Table A Table B[/FONT]

[FONT=Fixedsys]     1      00123   0123[/FONT]
[FONT=Fixedsys]     2      00345   0345[/FONT]
[FONT=Fixedsys]     3      00567[/FONT]
[FONT=Fixedsys]     4              0789[/FONT]
[FONT=Fixedsys]

With LEFT OUTER JOIN, the results included both entries from Rows 1 and 2, Table A's entry from Row 3, and nothing from Row 4

With RIGHT OUTER JOIN, the results included both entries from Rows 1 and 2, nothing from Row 3, and Table A's entry from Row 4

With INNER JOIN, the results included both entries from Rows 1 and 2, and nothing from Row 3 or Row 4

 

[lillyanna]

Yes, if there are no null fields, it is correct. But if I have null values in table B, then I will get an invalid use of null.

 

[MSAccessRookie]

Yes, if there are no null fields, it is correct. But if I have null values in table B, then I will get an invalid use of null.

INNER JOIN will not return any NULLs.

 

[lillyanna]

Yes, but it's a left outer join .. this shouldn't be so difficult, I feel like i'm missing something... :banghead::banghead:

 

[MSAccessRookie]

Yes, but it's a left outer join .. this shouldn't be so difficult, I feel like i'm missing something... :banghead::banghead:

The Query is reacting properly for a LEFT OUTER JOIN. It is taking all entries from Table A along with all Matching Entries from Table B, and when there is no entry in Table B, it substitutes a NULL Value.

If you truly need LEFT OUTER JOIN, you could try using Nz() to create a Default value that is not NULL. Something like the following is what I refer to:

SELECT A.id, Nz(B.id, "0000") FROM A LEFT OUTER JOIN B ON CInt(A.id)=CInt(B.id)

 

[DavidAtWork]

Can you not just use a WHERE clause instead a JOIN clause.
"SELECT A.id, B.id FROM A, B WHERE ((CInt(A.id)=CInt(B.id)) AND (B.id Is Not Null))"
David