So in my previous post I had looked at generating single SQL Nested IF statements using MS Access but had highlighted that Access will error out indicating the SQL is too complex should there be more than 13 nested IIFs in a single SQL.
What happens if you have 20 values that you require to be changed.
Answer = you pick 13 and produce one sql statement with 13 nested if statements and another with 7 nested IIF statements.
But damn it,doesn’t that introduces yet another manual step to what was supposed to be automation. Yes it does this is why I wrote this function which allows you to define the number of nestings and will go off and write the appropriate number. And yes I hear you DBAs saying a better way would be to link in the conversion table at the beginning – absolutely but there may be occasions where you can’t link to the required database.
The following iterates through a conversion table and writes the resulting SQL into a table named T005NestedIIFs – it should be noted that the Wend statement is artificially optomised here and if you have an extremely large code conversion table you may need to work on this code to make it dynamically alter the number of WEND statements depending on the BatchFileNo you wish to create and its relation to the number of records in the conversion table. The below code goes to the last record as part of the recordset count and when at 0 will jump to exit so for smaller code conversions its semi-dynamic but for larger files the limit of values converted with be the BatchFileSize * 200 which may or may not be big enough for purpose.
To have this working you will need two tables and knowledge of a third.
This should have 5 fields
The flag fields only require a single integer value. They are used to record how far through the conversion table you have reached so that when you break from your SQL query you resume at the finish of your former position.
The queries are placed in table
In which I have created 3 fields
One marked SQLfield
Public Function CreateTableofSQL(TargetTable As Variant, TargetFieldforUpdate As Variant, BatchSizeNo As Long) On Error GoTo Err_Writetofile Dim rst As DAO.Recordset Dim rst2 As DAO.Recordset Dim rst3 As DAO.Recordset Dim rst4 As DAO.Recordset Dim RecordCount1 As Long Dim RecordCount2 As Long Dim LCounter As Integer Dim SQLString1 As String LCounter = 1 While LCounter < 200 LCounter = LCounter + 1 SQLString1 = "" 'Three recordsets were testing differing flag fields because order of the integrity of the recordsets were being affected by each other Set rst2 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag2 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag2)<>1));") Set rst3 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag3 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag3)<>1));") Set rst = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag1 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag1)<>1));") 'MoveLast required to ensure correct record count at first pass reset to first after this rst.MoveLast RecordCount1 = rst.RecordCount rst.MoveFirst rst3.MoveLast RecordCount2 = rst3.RecordCount rst3.MoveFirst SQLString1 = SQLString1 & "UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "=" Do Until rst.EOF = True rst.Edit rst!xFlag1 = 1 rst.Update RecordCount1 = RecordCount1 - 1 SQLString1 = SQLString1 & "IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'" If RecordCount1 = 0 Then SQLString1 = SQLString1 & " " Else SQLString1 = SQLString1 & "," End If rst.MoveNext Loop rst.Close Do Until rst2.EOF = True SQLString1 = SQLString1 & ")" rst2.Edit rst2!xFlag2 = 1 rst2.Update rst2.MoveNext Loop SQLString1 = SQLString1 & " WHERE ((" rst2.Close Do Until rst3.EOF = True RecordCount2 = RecordCount2 - 1 SQLString1 = SQLString1 & "(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'" rst3.Edit rst3!xFlag3 = 1 rst3.Update If RecordCount2 = 0 Then SQLString1 = SQLString1 & " " Else SQLString1 = SQLString1 & " OR " End If rst3.MoveNext Loop rst3.Close SQLString1 = SQLString1 & "));" Set rst4 = CurrentDb.OpenRecordset("T005NestedIIFs") With rst4 .AddNew rst4!SQLfield = SQLString1 rst4!Targfield = TargetFieldforUpdate rst4!Targtable = TargetTable rst4.Update rst4.Close End With Wend Exit_WritetoFileError: Exit Function Err_Writetofile: If Err.Number = 3021 Then MsgBox "All records in Translation table written out no more to translate" Else MsgBox Err.Description End If Resume Exit_WritetoFileError End Function