Syntax error in UPDATE statement.

[MJ]

I am puzzled by this syntax error. I am using the same code over and over
again (with minor modifications to field names) and this error seems to pop
up.
Below is the code that is causing the problem, do any of you see what is
causing the syntax error?

'OE_Cardiac: CURRENT Hold Dollars
Q = "SELECT Sum([Table OE Errors, CURRENT].ClmAmt) AS SumOfAccts1 " & _
"FROM [Table: OEErrors, CURRENT] " & _
"WHERE (((([Table OE Errors, CURRENT].[AcctType])<>'C') AND " & _
"(([Table OEErrors, CURRENT].[AcctType])<>'S')) AND " & _
"(([Table OEErrors, CURRENT].[RespDept])='Cardiac Rehab' Or " & _
"([Table OEErrors, CURRENT].RespDept)='CR3413'));"
Set rsMyRS = dbMyDB.OpenRecordset(Q)
Q = "UPDATE [Table OEData, Dept CW] " & _
"SET [Table OEData, Dept CW].OE_Cardiac = " & rsMyRS("SumOfAccts1")
& _
" WHERE (([Table OEData, Dept CW].AMOUNT)='DOLLARS');"
qdMyQD.SQL = Q
DoCmd.OpenQuery ("qryUpdateData")

What has me puzzled is this same update query will work in the lines before
or after this segment.

 

[MJ]

In working some more with this problem... it appears that the "syntax error"
occurs whenever the results of the SELECT query is 0. Is there a simple way
to test for the value of the SELECT query before attempting to execute the
UPDATE query?

 

[John Spencer]

If rstMyRS.RecordCount > 0 then
'Process your record
....


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

In working some more with this problem... it appears that the "syntax error"
occurs whenever the results of the SELECT query is 0. Is there a simple way
to test for the value of the SELECT query before attempting to execute the
UPDATE query?

I am puzzled by this syntax error. I am using the same code over and over

again (with minor modifications to field names) and this error seems to pop
up.
Below is the code that is causing the problem, do any of you see what is
causing the syntax error?

'OE_Cardiac: CURRENT Hold Dollars
Q = "SELECT Sum([Table OE Errors, CURRENT].ClmAmt) AS SumOfAccts1 " & _
"FROM [Table: OEErrors, CURRENT] " & _
"WHERE (((([Table OE Errors, CURRENT].[AcctType])<>'C') AND " & _
"(([Table OEErrors, CURRENT].[AcctType])<>'S')) AND " & _
"(([Table OEErrors, CURRENT].[RespDept])='Cardiac Rehab' Or " & _
"([Table OEErrors, CURRENT].RespDept)='CR3413'));"
Set rsMyRS = dbMyDB.OpenRecordset(Q)
Q = "UPDATE [Table OEData, Dept CW] " & _
"SET [Table OEData, Dept CW].OE_Cardiac = " & rsMyRS("SumOfAccts1")
& _
" WHERE (([Table OEData, Dept CW].AMOUNT)='DOLLARS');"
qdMyQD.SQL = Q
DoCmd.OpenQuery ("qryUpdateData")

What has me puzzled is this same update query will work in the lines before
or after this segment.