I came across a situation at work where we were needing to alter a large number of values in particular fields from one value to another. Here is a function I put together to assist in this. After some thought it was obvious that this would be better accomplished joining the conversion table in the query editor and moving on from there, particularly because MS Access has a limit on the number nested IIFs allowed in a single statement. Nonetheless I publish it here as it may prove useful.
Here I create a table T001CodeConversionTable that holds the translation from one set of codes to another. This field also has to have several fields in it named
The variables TargetTable and TargetFieldforUpdate exist in the table that will have the resultant SQL performed on it.
Public Function CreateNestedIF(TargetTable As Variant, TargetFieldforUpdate As Variant) Dim rst As DAO.Recordset Dim rst2 As DAO.Recordset Dim rst3 As DAO.Recordset Dim RecordCount1 As Long Dim RecordCount2 As Long Set rst = CurrentDb.OpenRecordset("T001CodeConversionTable") Set rst2 = CurrentDb.OpenRecordset("T001CodeConversionTable") Set rst3 = CurrentDb.OpenRecordset("T001CodeConversionTable") RecordCount1 = rst.RecordCount RecordCount2 = rst3.RecordCount Dim fs, TextFile Set fs = CreateObject("Scripting.FileSystemObject") Set TextFile = fs.CreateTextFile("C:\Users\Mark\Documents\NestedIFs.txt", True) TextFile.WriteLine ("UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "=") Do Until rst.EOF = True RecordCount1 = RecordCount1 - 1 TextFile.WriteLine ("IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'") If RecordCount1 = 0 Then TextFile.WriteLine ("") Else TextFile.WriteLine (",") End If rst.MoveNext Loop rst.Close Do Until rst2.EOF = True TextFile.WriteLine (")") rst2.MoveNext Loop TextFile.WriteLine ("WHERE ((") rst2.Close Do Until rst3.EOF = True RecordCount2 = RecordCount2 - 1 TextFile.WriteLine ("(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'") If RecordCount2 = 0 Then TextFile.WriteLine ("") Else TextFile.WriteLine ("OR") End If rst3.MoveNext Loop rst3.Close TextFile.WriteLine ("));") TextFile.Close MsgBox "Created NestedIFs File in C drive" End Function