[AccessD] SQL concatenation

William:
It looks as though you have a stray ) in your statement, as in:
[tblOrg]![Country]) AS CSC

I don't see a corresponding open paren which would account for the syntax error.

James Barash 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Monday, February 02, 2009 10:48 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] SQL concatenation

Group

...this works fine

lstFound.RowSource = "SELECT DISTINCTROW tblOrg.OrgID, tblOrg.OrgName, 
tblOrg.Address, tblOrg.City, tblOrg.StateOrProvince, tblOrg.Country, 
tblOrg.Buyer, tblOrg.Inactive FROM tblOrg WHERE (((tblOrg.OrgName) Like 
[txtSearchExpression]) And ((tblOrg.Buyer) = True) And ((tblOrg.Inactive) = 
False)) ORDER BY tblOrg.OrgName;"

...but when I try to concatenate 3 of the fields, I get a syntax error

lstFound.RowSource = "SELECT DISTINCTROW tblOrg.OrgID, tblOrg.OrgName, 
tblOrg.Address, [tblOrg]![City] & ", " & [tblOrg]![StateOrProvince] & " " & 
[tblOrg]![Country]) AS CSC, tblOrg.Buyer, tblOrg.Inactive FROM tblOrg WHERE 
(((tblOrg.OrgName) Like [txtSearchExpression]) And ((tblOrg.Buyer) = True) 
AND ((tblOrg.Inactive) = False)) ORDER BY tblOrg.OrgName;"

...any help appreciated.

William