Update statement doesn't update

[serge castle]

Hello,

I'm kind of newby in VBA/SQL in access.

I'm trying to write a code that sums several columns and the result should be "updated" to a Total record.

I get no error message, but it doesn't work.

What can be wrong?

Here's part of the code.

    sqlselect = "SELECT * From 12MonthsTemp"
    Set objrs = conn.Execute(sqlselect)
    If (objrs.EOF <> True) Then
        nrows = objrs.GetRows
 
        For i = 0 To UBound(nrows, 2)
            bdate = nrows(0, i)
            edate = nrows(1, i)
            amnt = nrows(3, i)
 
            For j = 8 To 147
                SumRws = SumRws + nrows(j, i)
            Next j
 
            tableupdate = "UPDATE 12MonthsTemp SET [Total Recognition]= " & SumRws & " WHERE ([Begin Date]='" & bdate & "') AND ([End Date]='" & edate & "') AND ([Invoice Amount]=" & amnt & ");"
            conn.Execute (tableupdate), NumOfRec, dbFailOnError
            Debug.Print NumOfRec & " records were updated."
            SumRws = 0
        Next
    End If
 

Any help should be appreciated.

[Rabbit]

If those date fields are actually date fields, you need to specify that they're dates using hash (#) symbols, not single quotes (').

[serge castle]

Thank you for your response, but it still doesn't update the Total Recognition field...maybe I need another approach?

[Rabbit]

Did you have the code output the update statement and seeing if you can manually run it?

[NeoPa]

Please check out Debug SQL String before continuing with this question.

[serge castle]

UPDATE 12MonthsTemp SET [Total Recognition]= -134255.64 WHERE ([Begin Date]=#5/1/2007#) AND ([End Date]=#4/30/2008#) AND ([Invoice Amount]=-11187.97);

[Rabbit]

And does that run when you do it manually?

[serge castle]

Rabbit, I'm not sure what do you mean by "manually run it". I use the DoCmd.RunSQL but I get this message "You are about to update 0 rows".
I also run it by hiting F8 key, with the same result:No error message, but not updating the table....

[Rabbit]

I mean create a new query, paste in the SQL, and run it. If there are errors, then your dynamic SQL string is incorrectly syntaxed and the error message will give you an idea as to what the problem is. If there is no error message, but it doesn't do what you want, then there isn't anything syntactically wrong but something else is wrong with the string, perhaps with how the filters are being set up.

[NeoPa]

Serge:
I use the DoCmd.RunSQL but I get this message "You are about to update 0 rows".

This means the criteria you have specified matches no records. I suggest you check your data.

PS. Assuming all the names used are correct, the SQL you posted is formatted perfectly OK.

[Rabbit]

What you should do is start with a select query and once you can consistently select the record you want, make the update query's where clause the same as your select query's where clause.

[serge castle]

NeoPa, you were right.

I changed the criteria and it worked like a charm.

Thank you, very much!

[serge castle]

Thank you Rabbit for your response, it seems that the "Where" criteria was ambiguos or something.
Change it and it work!

[NeoPa]

I'm very pleased you managed to get it sorted :-)