update table from report

[patricktwo]

I am running a report and on the close I want to update a table in VBA. I am using the following SQL.

Private Sub Report_Close()
Dim strSQL As String
strSQL = "UPDATE checks set rptStateone = yes, pdToState = yes "
strSQL = strSQL & "WHERE (pdToState = no) AND (rptToState = NO) and (pdToPayee = no) AND "
strSQL = strSQL & &quot;(amount < [Report]![firststatereporta]!remitAmt]) and &quot;
strSQL = strSQL & &quot;(checkDate < DateAdd('yyyy',-[Report]![firststatereporta]![nrYears], Date())) and &quot;
strSQL = strSQL & &quot;((ownID) IN (SELECT payeeID from payee WHERE payeestate = [Report]![firststatereporta]![stateAbr]))&quot;
CurrentDb.Execute strSQL
End Sub

I keep getting a syntax error when I run it in the report.
Any help would be greatly appreciated.

 

[AWithers]

Just a thought, have you tried putting a break point in before you execute the string then copying the value of strsql into the sql window of a new query just to check you have got the syntax right, you can then switch back to the qbe grid. If the query looks right then it is something else. I often spot a missing space or bracketing being wrong

alternatively create the query you want using qbe and then copy and paste the sql into the code breaking it up as necessary to insert your variables

Andy

 

[patricktwo]

I'll try the first one.

I originally used qbe to created the query, but it used joins and I got a message that joins were not supported. I then rewrote it without the join.

 

[patricktwo]

I found a missing bracket at remitAmt. Now I get error &quot;3061 to few parameters expected 5.&quot;