Left Join in Query. 1

[lars7]

Hi I'm trying to add a third table to my query but I am getting a symtax error. Can anyone show me how to fix it.

strSQL = "Select * FROM [tbltruststaff] LEFT JOIN [Service Descriptions] ON [tbltruststaff].DIV = [Service Descriptions].D_DIV LEFT JOIN [Discipline Descriptions] ON [tbltruststaff].DISCIPLINE = [Discipline Descriptions].D_DISCIPLINE" _

It works well with the first left join but not the second.

 

[Golom]

You need parentheses

strSQL = "Select * FROM [COLOR=red]([/color][tbltruststaff] LEFT JOIN [Service Descriptions] ON [tbltruststaff].DIV = [Service Descriptions].D_DIV [COLOR=red])[/color] LEFT JOIN [Discipline Descriptions] ON [tbltruststaff].DISCIPLINE = [Discipline Descriptions].D_DISCIPLINE"

 

[lars7]

hi Golam,
I tried that but still "(the missing operator) in query expression" message. I have a few where's in the code too, could this cause the error:

strSQL = "Select * FROM ([tbltruststaff] LEFT JOIN [Service Descriptions] ON [tbltruststaff].DIV = [Service Descriptions].D_DIV) LEFT JOIN [Discipline Descriptions] ON [tbltruststaff].DISCIPLINE = [Discipline Descriptions].D_DISCIPLINE" _
& "Where tbltrustStaff.

is null AND " _
& "tbltruststaff.[additpayno] = '1' and " _
& "tbltruststaff.[grade] <>'bn' and " _
& "tbltruststaff.[grade] <>'EXEC LVL 4' and " _
& "tbltruststaff.[grade] <>'EXEC LVL 5' and " _
& "tbltruststaff.[grade] <>'EXEC LVL 6' and " _
& "tbltruststaff.[grade] <>'EXEC LVL 7' and " _
& "tbltruststaff.[grade] <>'EXEC LVL 8' and " _
& "tbltrustStaff.[transfer] = 0 " _
& strwhere

This is the line that's showing in the error message:


[tbltruststaff].DISCIPLINE = [Discipline Descriptions].D_DISCIPLINE" _
& "Where tbltrustStaff.

is null AND " _​

 

[lars7]

I have just commented out all the Where's in the query and it worked but i need to keep them, has anyone any suggestions.

 

[lars7]

Hi,
I got it to work. I changed the join to an Inner and it worked. Thanks Golom for the parentheses.

 

[Golom]

Now you're missing a space

Descriptions].D_DISCIPLINE[COLOR=red yellow] [/color]" _
        & "Where tbltrustStaff.[left] is null AND " _

 

[lars7]

I must have changed that when I was copying and pasting things in, it's gone now.

Thanks again.