If..Then..Else and Update query

[jane30]

Hi, With Access97, I update f1 based on more than 3 conditions, e.g if f2=a and f3=b, f1=xxx else if f2=a or f3>b, f1 = yyy, etc) it'll be too complicated if I use iif() statement in update query. So, I used VBA code instead. The table with f1 in it has 70,000 records. Here is my code:
Function updt()

Dim db As Database, rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Non-MM visits test&quot

Dim tbl As String, f1 As String, sql As String, sql2 As String, sql3 As String

tbl = "[non-MM visits test]"
f1 = "[visit factor]" '(single field)

rst.MoveFirst
Do Until rst.EOF

sql = "Update " & tbl & " set " & tbl & "." & f1 & "=1"
sql2 = "Update " & tbl & " set " & tbl & "." & f1 & "=" & tbl & ".[mccalc perc]"
sql3 = "Update " & tbl & " set " & tbl & "." & f1 & "=" & tbl & ".[mcal calc paid]/100"

If rst("mcal paid&quot = 0 Then
If rst("Mcare Paid&quot = 0 Then
DoCmd.RunSQL (sql)
Else
DoCmd.RunSQL (sql2)
End If
ElseIf rst("mcal paid&quot > 0 And rst("mcare paid&quot = 0 Then
If rst("mcal paid&quot = 34 Or 436 Or 550 Or 654 Then
DoCmd.RunSQL (sql)
Else
DoCmd.RunSQL (sql3)
End If
End If

rst.MoveNext
Loop
rst.Close

End Function

I only want the update query to run once, depends on the if statement, for each record it read. But it seemed to run several times from the status bar. What's wrong? Thanks a bunch.

 

[skiflyer]

Do you mean once per record?

As the loop is set up, you're going to be testing that if statement on every record, and possibly running the updated query 70,000 times. And I'm a little rusty, but isn't your update query running on the whole table? not a per record basis?

It appears to me the final value of your entire table is going to rely on where the last record evalutes in your if statement.

Here's a question, do you maybe want something like

UPDATE tbl
SET f1 = 1
WHERE "mcal paid" = 0 AND "mcare paid" = 0


UPDATE tbl
set f1 = "tbl.mcal calc paid"/100
WHERE "mcal paid" > 0 AND "mcare paid" = 0


UPDATE tbl
SET f1 = 1
WHERE "mcal paid" >0 AND "mcare paid" = 0 AND ("mcal paid" = 34 OR "mcal paid" = 436 OR "mcal paid" = 550 OR "mcal paid" = 654)


UPDATE tbl
set f1 = "tbl.mcalc perc"
WHERE &quot;mcal paid&quot; = 0 AND &quot;mcare paid&quot; <> 0


I believe running the query in that order does everything you want (I'm not sure how to put the &quot;'s and such to reference the fields).

OTHERWISE, if you want to stick with recordsets and such, don't run an SQL update, just use the recordset to set the fields and update the recordset. I just finished writing a program minus the error checking that does that which I can cut'n'paste for you if you're interested.

 

[crpjaviman]

jane30,

Have you tried use the Select...Case form?

Otherwise, a proven method that the table will be queried once is to work off the .recordcount. I believe that this will make sure that the record is queried once.

One more suggestion, try it with the SQL statements outside of the loop.

This is what I can help with.

Laters! Thanks,
crpjaviman