have a field name that is separated by a spacethat i need to reference in a
sql statement. i know i can put brackets around the field name, but the sql
statement also has brackets. how can i code? i have tried putting quotes,
extra brackets, parens around the field name but nothing works. here is the
sql statement


sqlSelect = "SELECT AllClaims.* FROM[SELECT Src.*,
Nz(P2P.ProductionCode,'MISSING CODE') AS ProdCode FROM cntrlD_ITP" &
strthisgrpid & " AS Src LEFT JOIN PendToProduction AS P2P ON Src.[Pend code]
= P2P.PendCode]. AS AllClaims LEFT JOIN tbl_Assignments AS Asgnd ON
AllClaims.[Claim Number] = Asgnd.[Claim Number] WHERE Asgnd.Assignee Is Null"
sqlOrderBy = " ORDER BY Age DESC"


thanks for your help

On Fri, 7 May 2010 14:38:01 -0700, jnewl <(E-Mail Removed)>
wrote:

>have a field name that is separated by a spacethat i need to reference in a
>sql statement. i know i can put brackets around the field name, but the sql
>statement also has brackets. how can i code? i have tried putting quotes,
>extra brackets, parens around the field name but nothing works. here is the
>sql statement
>
>
>sqlSelect = "SELECT AllClaims.* FROM[SELECT Src.*,
>Nz(P2P.ProductionCode,'MISSING CODE') AS ProdCode FROM cntrlD_ITP" &
>strthisgrpid & " AS Src LEFT JOIN PendToProduction AS P2P ON Src.[Pend code]
>= P2P.PendCode]. AS AllClaims LEFT JOIN tbl_Assignments AS Asgnd ON
>AllClaims.[Claim Number] = Asgnd.[Claim Number] WHERE Asgnd.Assignee Is Null"
>sqlOrderBy = " ORDER BY Age DESC"
>
>
>thanks for your help

Unfortunately, you can't! A Subquery cannot have any square brackets in it.

Your best bet would be to save the subquery as a query in its own right,
aliasing the fieldnames.
--

John W. Vinson [MVP]

This may help:
http://allenbrowne.com/ser-71.html
http://www.fontstuff.com/access/acctut15.htm

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"jnewl" wrote:

> have a field name that is separated by a spacethat i need to reference in a
> sql statement. i know i can put brackets around the field name, but the sql
> statement also has brackets. how can i code? i have tried putting quotes,
> extra brackets, parens around the field name but nothing works. here is the
> sql statement
>
>
> sqlSelect = "SELECT AllClaims.* FROM[SELECT Src.*,
> Nz(P2P.ProductionCode,'MISSING CODE') AS ProdCode FROM cntrlD_ITP" &
> strthisgrpid & " AS Src LEFT JOIN PendToProduction AS P2P ON Src.[Pend code]
> = P2P.PendCode]. AS AllClaims LEFT JOIN tbl_Assignments AS Asgnd ON
> AllClaims.[Claim Number] = Asgnd.[Claim Number] WHERE Asgnd.Assignee Is Null"
> sqlOrderBy = " ORDER BY Age DESC"
>
>
> thanks for your help

John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in
news:(E-Mail Removed):

> On Fri, 7 May 2010 14:38:01 -0700, jnewl
> <(E-Mail Removed)> wrote:
>
>>have a field name that is separated by a spacethat i need to
>>reference in a sql statement. i know i can put brackets around the
>>field name, but the sql statement also has brackets. how can i
>>code? i have tried putting quotes, extra brackets, parens around
>>the field name but nothing works. here is the sql statement
>>
>>
>>sqlSelect = "SELECT AllClaims.* FROM[SELECT Src.*,
>>Nz(P2P.ProductionCode,'MISSING CODE') AS ProdCode FROM cntrlD_ITP"
>>& strthisgrpid & " AS Src LEFT JOIN PendToProduction AS P2P ON
>>Src.[Pend code] = P2P.PendCode]. AS AllClaims LEFT JOIN
>>tbl_Assignments AS Asgnd ON AllClaims.[Claim Number] =
>>Asgnd.[Claim Number] WHERE Asgnd.Assignee Is Null" sqlOrderBy = "
>>ORDER BY Age DESC"
>
> Unfortunately, you can't! A Subquery cannot have any square
> brackets in it.
>
> Your best bet would be to save the subquery as a query in its own
> right, aliasing the fieldnames.

Also, get rid of the practice of using spaces and non-alphanumeric
characters in object names!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/