[AccessD] SQL concatenation

Here's my best advice, drop in a Debug.Print statement so VBA will print the 
evaluated statement in the Immediate window. Then, cut and paste the 
evaluated statement from the Immediate window into a SQL window -- for some 
reason, the SQL window gives more specific and helpful error messages.

Susan H.
----- Original Message ----- 
From: "William Hindman" <wdhindman at dejpolsystems.com>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Monday, February 02, 2009 10:47 AM
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