brackets in a sql statement

[jnewl]

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]

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.

 

[ryguy7272]

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

 

[David W. Fenton]

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!