Access syntax error when using Join

Question

I have those two tables (Members and Now) I just need to make sure that no one in Members is actually in Now. Both tables have different structures but can be joined on firsname, lastname and postalcode.

So I tried this (in access)

SELECT Members.Prenom, Members.Nom, Members.Adresse, Members.[Adresse 2], Members.ville, Members.Province, Members.CodePostal
FROM Members
Left JOIN now ON (members.prenom = now.firstname AND members.nom = now.lastname
AND members.codepostal = now.postcode) WHERE now.id IS NULL

And it gives me a wonderful error message

invalid use of '.' ' ' or '()'. in query expression

May someone enlighten me on what I did wrong?

Members and members are not the same thing. Try matching the casing and see if your error is different — Kai Qing
– Kai Qing, Commented May 6, 2014 at 23:53
Pretty sure now is a restricted keyword? try wrapping `` around the columns — Menztrual
– Menztrual, Commented May 6, 2014 at 23:53

Simon1979 · Accepted Answer · 2014-05-07 01:30:01Z

1

Pretty sure you cannot use 'now' as a table name, there are certain reserved words that MS Access need (in this case for function Now(), I guess the error message is telling you have missed the parentesis' ()). You could try encasing it in square brackets but I would strongly recommend changing your table name. A useful format I use is to prefix objects such as tblTableName, qryQueryName, rptReportName, frmFormName etc but whatever works for you.

SELECT Members.Prenom, Members.Nom, Members.Adresse, Members.[Adresse 2], 
    Members.ville, Members.Province, Members.CodePostal
FROM Members
Left JOIN [now] a ON (members.prenom = a.firstname AND members.nom = a.lastname
AND members.codepostal = a.postcode) WHERE a.id IS NULL

answered May 7, 2014 at 1:30

Simon1979

2,1081 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access syntax error when using Join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related