Power By 专否社区

How to do a loop with update query - Access

Go To StackoverFlow.com

2

I' am trying to do a loop that would update table Cleaned with a source code from table DataSelect, if the criteria matches to the DataSelect table. Example of the criteria are like if the year is between 2012 and 2011 and Channel is like TM and MRC is between 5 and 10. I create DataSelect to hold all the different types of criteria I want the program to loop through each one an assign in Cleaned the sourcecode. So far I always am getting a "Too few parameters. Expect 1. Run Time error 3061." Any help would be greatly appreciated.

Thanks, Niki

Private Sub doDataSegm_Click()


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strSearch, strReplace As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("dataselect", dbOpenTable)

If rs.RecordCount = 0 Then Exit Sub
rs.MoveFirst

    For i = 1 To rs.RecordCount
    strChan = rs("Chan")
    strDataCode = rs("code")
    strMrcYrLow = rs("mrcyr_low")
    strMrcYrhigh = rs("mrcyr_high")
    strMrcLow = rs("mrc_low")
    strMrchigh = rs("mrc_high")
    strSQL = "UPDATE Cleaned SET [cleaned].[datacode]= " & _
              strDataCode & " where [CLEANED].[CHANNEL] Like '" & strChan & _
             "' AND [CLEANED].[MRC_YEAR] Between '" & strMrcYrLow & _
             "' And '" & strMrcYrhigh & "' AND CLEANED.MRC Between " & _
              strMrcLow & " And " & strMrchigh & ";"

    db.Execute strSQL, dbFailOnError
    rs.MoveNext
    Next i


End Sub
2012-04-04 19:21
by user1313666
What line is giving that error? If it's the db.Execute... one, have you tried using debug.print to show the content of strSQL, and then running that directly in Access ? First thing to do is check your datatypes match the quoting in your SQL. For example, is MRC_YEAR really not a numeric field - Tim Williams 2012-04-04 19:54
Does the rs("code") field have numeric data in it? If not, you need to quote the identifier. Same with rs("mrc_low") and rs("Mrc_high") - mellamokb 2012-04-04 21:33

0

A message like "Too few parameters. Expect 1. Run Time error 3061." is usually an indication that your query contains an unquoted data value, which Access is interpreting instead as a field name. Since the only unquoted identifiers in your query are datacode and MRC, it looks like you need to quote one of them:

UPDATE Cleaned SET [Cleaned].[datacode] = '" & strDataCode & "' ...
                                          ^ ...add quotes...  ^
2012-04-04 21:33
by mellamokb
Thanks for all the feedback the adding in the extra ' in front of " seemed to do the trick. One other question, is there a way to set it to run in order. For instance, the dataselect table to run from row 1 down to row 10. How should I set the program to do that - user1313666 2012-04-04 22:04
Ads