[AccessD] SQL concatenation

...thanks to all who responded so quickly

...Mark caught the extra parentheses but it was just a leftover from me 
experimenting, not the real problem

...Charlotte took me to the woodshed and administered a sound beating ...I 
had built the concat in the query designer and just copied the SQL over from 
there ...it gave me the bangs and the double quotes ...as Charlotte 
admonished, replacing them was the real answer ...lesson learned ...I'm sure 
the welts will heal :)

William

--------------------------------------------------
From: "Charlotte Foust" <cfoust at infostatsystems.com>
Sent: Monday, February 02, 2009 11:31 AM
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>