[AccessD] SQL concatenation

William,

If tblOrg is your table and StateOrProvince is the field, why are you
using the bang (!) to delimit them in the second statement, and what are
you trying to do?  If you want to concatenate the fields within the SQL
statement, don't use double quote around the commas, use single quotes.
Otherwise, you're trying to set a rowsource to "Something", "Something",
"Something else".

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
Hindman
Sent: Monday, February 02, 2009 7: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
 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com