Join Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Allen Davidson - 09 Jun 2007 18:16 GMT

Hi Im getting the error "(missing operator) in query expresion" .... it then
prints part of my join

Full text of the join from the immediate window is

SELECT r.ID, FirstName, LastName, Gender, DateDiff("yyyy",DateOfBirth,Now())
AS AgeNow, SocialClass, IIF(ISNULL(cr.CaseID),'','Short Listed') AS
ShortList FROM tblRespondent r LEFT JOIN tblCaseRespondent cr ON (r.ID =
cr.RespondentID AND cr.CaseID = 9) LEFT JOIN tblChild c ON (tblRespondent.ID
= tblChild.RespondentID)

Any ideas? I'm using ADODB and creating the sql inline

Thanks
Allen

Reply to this Message

Douglas J. Steele - 09 Jun 2007 19:08 GMT

Access tends to use non-standard SQL for joins.

I'd recommend building the query in the graphical builder, then getting the
SQL from there. I think you'll find it'll be something like

FROM Table1 LEFT JOIN (Table2 LEFT JOIN Table3 ON Table2.ID = Table3.ID) ON
Table2.ID = Table1.ID

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Im getting the error "(missing operator) in query expresion" .... it
> then
[quoted text clipped - 14 lines]
> Thanks
> Allen

Reply to this Message

Allen Davidson - 09 Jun 2007 19:34 GMT

Thanks Douglas - will try - does Access behaviour change on this if I turn
on Ansi 92 syntax?

> Access tends to use non-standard SQL for joins.
>
[quoted text clipped - 22 lines]
> > Thanks
> > Allen

Reply to this Message

Douglas J. Steele - 09 Jun 2007 21:03 GMT

To be honest, I've never checked.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks Douglas - will try - does Access behaviour change on this if I
> turn
[quoted text clipped - 29 lines]
>> > Thanks
>> > Allen

Reply to this Message