Update query with six (6) IIF update rules... please help to identify most efficient way processing

[skydivetom]

Experts:

I need some assistance with writing an UPDATE query based on multiple conditions.

Background first...
1. Attached DB contains two (2) tables: [01_tbl_Before] and [02_tbl_After] where "02" is a current copy of "01"
2. [01_tbl_Before] contains 2 fields: [T1] and [T2]

Please see below the six (6) business rules (BR) that need to be integrated into an UPDATE query.

1. IIF ([T1] = 15 AND [T2]) = 15 THEN [T2] = NULL
2. IIF ([T1] = 15 AND [T2]) = (100 through and incl. 120) THEN [T1] = 10
3. IIF ([T1] = 15 AND [T2]) = (130 through and incl. 160) THEN [T1] = 11
4. IIF ([T1] = 15 AND [T2]) = (170 through and incl. 190) THEN [T1] = 12
5. IIF ([T1] = 15 AND [T2]) = (200 through and incl. 230) THEN [T1] = 13
6. IIF ([T1] = 15 AND [T2]) = (240 through and incl. 275) THEN [T1] = 14



I presume the above BR are (I hope self-explanatory) and don't require further information. If needed, however, I'll gladly expand on them.

Here's what I need some help with:
Although I have created IIF statements before and then used them as an UPDATE query, I'm not 100% confident that *stringing* these 6 BRs into a single IIF statement is the most efficient way to update my records.

Thus, my question: What is the most efficient way to update those records (either field [T1] and/or [T2]) where the conditions of the 6 BRs are met?

Thank you,
Tom

[orange]

Tom,

Does it have to be a query?

'1. IIF ([T1] = 15 AND [T2]) = 15 THEN [T2] = NULL
'2. IIF ([T1] = 15 AND [T2]) = (100 through and incl. 120) THEN [T1] = 10
'3. IIF ([T1] = 15 AND [T2]) = (130 through and incl. 160) THEN [T1] = 11
'4. IIF ([T1] = 15 AND [T2]) = (170 through and incl. 190) THEN [T1] = 12
'5. IIF ([T1] = 15 AND [T2]) = (200 through and incl. 230) THEN [T1] = 13
'6. IIF ([T1] = 15 AND [T2]) = (240 through and incl. 275) THEN [T1] = 14

Sub testing()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("01_tbl_before")
    Do While Not rs.EOF
        rs.Edit
        Debug.Print rs!t1 & "  " & rs!t2
        If rs!t1 = 15 Then
            Select Case rs!t2
                Case 15
                    rs!t2 = Null
                Case 100 To 120
                    rs!t1 = 10
                Case 130 To 160
                    rs!t1 = 11
                Case 170 To 190
                    rs!t1 = 12
                Case 200 To 230
                    rs!t1 = 13
                Case 240 To 275
                    rs!t1 = 14
            End Select
        End If
        rs.Update
        rs.MoveNext
    Loop


End Sub

[skydivetom]

Orange... wow... this is great. I definitely like this approach much better than a query.

Two things:
I'm only updating [T2], so I changed the rs!t1 to rs!t2.
Next, some values (e.g., 200, 205, 210, 215, 220, 225) are not converted into the 2-digit number. I'll have a closer look to figure out why those values (a total of 357 values out of 2,444 values) are NOT converted.

Again, while I need to determine why 357 values are not picked up for conversion, I definitely love this process!!!

[orange]

Tom,

I had an error in the original code, I since updated it

Check that this is in your test set up. I had a dash in the original

Case 200 To 230
rs!t1 = 13

[CJ_London]

OP has a result

[skydivetom]

Orange -- per post #5 you were correct about the T1/T2 conversion. The BRs specified in the original post are correct.

I definitely prefer the module (over a query)... 'much easier to add/modify if the BRs change and/or expand. Thank you for this function... 'very much appreciated!!

[skydivetom]

Jack -- good morning... I posted a new (related) thread a moment ago.

I certainly would welcome your feedback...

https://www.accessforums.net/showthr...308#post479308