Recordset with SQL "OR" Statment

[CementCarver]

Hello All,

I'm trying to build a recordset joining two tables together.
I can get the join to work, but I need to use an "or" statement to
fill my rs.values

Here my statement so far:

mocommand.CommandText = "Select JUNK.[MS], JUNK.[CNT]," & _
" LINK.[FROM], LINK.[TO] from JUNK, LINK " & _
" where JUNK.[CNT] = LINK.[FROM] or " & _
" where JUNK.[CNT] = LINK.[TO]"

I'm getting an error with the syntax.

Basically, I'm trying to either find one matching value or another. I need to find the first matching value (junk.cnt = link.from), or the second one where (junk.cnt = link.to) using an "OR" statement.

Can anyone guide me as per the correct syntax?

....CementCarver

 

[Moniker]

Is LINK the name of a table? It shouldn't be as that's confusing as hell, and I don't think it is, but that's where the issue is. What you want is an INNER JOIN. It will look something like this:

SELECT 
    Junk.MS
    ,Junk.Cnt
    ,Link.From
    ,Link.To 
FROM
    Junk
INNER JOIN 
    Link ON
        Junk.CNT = Link.From OR
        Junk.CNT = Link.To
;

Just remember that naming a table LINK (and using all caps for that matter) is fairly confusing. Also, naming fields "From" and "To" is a big no-no, as both are keywords in VBA/Access. Be more descriptive, even if it's FromDate and ToDate.

 

[CementCarver]

Thx Moniker,

I will give your suggestion a try.

CementCarver