brackets in a sql statement

  • Thread starter Thread starter jnewl
  • Start date Start date
J

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
 
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
Click to expand...

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.
 
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"
Click to expand...

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.
Click to expand...

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