Update empty field with value from recordset 1

[Fozzy9767]

I am building a table of totals that are rolled up from groups. I can get all my totals into the table by looping through a rs that changes my sql and then appends the results to the table.

I have 2 fields that I need to update on each loop with the values from my rs. I cannot seem to get it to work, I have tried several different approaches. I am adding one row per loop and so the only empty fields are on the last row added. Each row has different totals and values in the last 2 fields. My table is rollupvolume, the 2 fields I need to update are agent and sort.

Both fields are text. I don't want to update either field if they are not empty.

Here is my current code trying to update the 2 fields in question:

strSQL = "Update RollupVolume Set agent to " & rs(1).Value & ", sort to " & rs(0).Value & vbCrLf & "WHERE NZ(rollupVolume.sort," & """" & ") & " & """" & "=" & """" & ";"
DoCmd.RunSQL strSQL

I get a "syntax error" with this one. If I just update the field with an arbitrary value it works fine, but I think the where clause is giving me fits. Any suggestions? Thanks, Ken

 

[PHV]

Perhaps this ?

strSQL = "UPDATE RollupVolume SET [agent]='" & rs(1).Value & "', [sort]='" & rs(0).Value & "' WHERE Trim([sort] & '')=''"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Fozzy9767]

Once again you saved me! I hate those pesky quotes! I hate doing this stuff by hand worse. Thanks.