Can anyone explain why this doesn't work?

[mrb783]

I have the following code in a standard Access 2003 query that works fine:

SELECT tbl_MasterSubCaseIDQuery.CaseNumber, tbl_MasterSubCaseIDQuery.CaseID, tbl_MasterSubCaseIDQuery.SubcaseID, '' AS CustodianID, '' AS EvidenceID, '' AS LogID
FROM tbl_MasterSubCaseIDQuery
WHERE tbl_MasterSubCaseIDQuery.CaseID=[number];

And here it is again in VBA format:

SQL = "SELECT tbl_MasterSubCaseIDQuery.CaseNumber, tbl_MasterSubCaseIDQuery.CaseID, tbl_MasterSubCaseIDQuery.SubcaseID, '' AS CustodianID, '' AS EvidenceID, '' AS LogID" & _
"FROM tbl_MasterSubCaseIDQuery" & _
"WHERE tbl_MasterSubCaseIDQuery.CaseID = " & Me.CaseID.Value

The problem is, I get the following error when I attempt to run the code:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Any idea why?

 

[mrb783]

Oh, and I'm using the following execution code to run it:

Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)

This works fine with other similar scripts.

 

[pbaldy]

No spaces between lines. Add

Debug.Print SQL

and you should see the problem in the Immediate window.

 

[mrb783]

I added the line and nothing changes. I get the same error, with no indication as to why.

 

[mrb783]

Oh, disregard that last post... Anyway, I figured it out, and the no spaces thing was spot on. Thanks a ton!