Loop through one less than the number of a recordset (1 Viewer)

Lightwave

Ad astra
Local time
Today, 20:20
Joined
Sep 27, 2004
Messages
1,521
I am trying to develop some code to create SQL that updates a field in a table using nested IIFs I am most of the way there. I create three recordsets and loop through each refering to a table which will hold the old and new values to be in each of the IIF statements the code below all but works

Code:
Public Function CreateNestedIF(TargetTable As Variant, TargetFieldforUpdate As Variant)

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("T001CodeConversionTable")
Set rst2 = CurrentDb.OpenRecordset("T001CodeConversionTable")
Set rst3 = CurrentDb.OpenRecordset("T001CodeConversionTable")

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
TextFile.WriteLine ("IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "',")
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
TextFile.WriteLine ("(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "' OR")
rst3.MoveNext
Loop

rst3.Close


TextFile.WriteLine ("))")


TextFile.Close


MsgBox "Created NestedIFs File in C drive"

End Function

In my example database this creates the following text file

Code:
UPDATE T003TargetTable SET T003TargetTable.TF=
IIF((T003TargetTable!TF='SEC1DC'),'S1DC',
IIF((T003TargetTable!TF='SEC1FQ'),'S1FQ',
IIF((T003TargetTable!TF='SEC6DCDOM'),'S6DCD',
IIF((T003TargetTable!TF='SEC6DCNONDOM'),'S6DCND',
IIF((T003TargetTable!TF='SEC6FQ'),'S6FQD'[U],[/U]
)
)
)
)
)
WHERE ((
(T003TargetTable!TF)='SEC1DC' OR
(T003TargetTable!TF)='SEC1FQ' OR
(T003TargetTable!TF)='SEC6DCDOM' OR
(T003TargetTable!TF)='SEC6DCNONDOM' OR
(T003TargetTable!TF)='SEC6FQ' [U]OR[/U]
))

Fine except the two underlined characters a ',' comma and the last 'OR' should not be added. I understand that I need some kind of switch to determine whether I am on the last of each of the recordsets that are working on the IIF statement and the Where filter such that if the program is not on the last record then place the appropriate character else move on.

I was thinking

If rst.lastrecord then
TextFile.WriteLine("")
Else
TextFile.WriteLine("OR")
End IF

Has anyone got a pointer?

Thanks
 
Last edited:

Lightwave

Ad astra
Local time
Today, 20:20
Joined
Sep 27, 2004
Messages
1,521
Hi all - funny how the discipline of explaining the problem often leads to you solving your own problem - I created two new variables counted the recordset and then each time I looped through the recordset reduced the relevant recordset count by 1 and then tested to see whether the remaining recordest was 0 if it was I skipped the last comman or OR.

Here's the code

Code:
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
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 13:20
Joined
Oct 17, 2014
Messages
3,506
Why are you doing this this way? If you just want to update the field TF from some values to some other values why not create a translation table with two columns that maps these changes and then create an update query that joins the T003TargetTable with this translation table?
 

Lightwave

Ad astra
Local time
Today, 20:20
Joined
Sep 27, 2004
Messages
1,521
Absolutely - I am in the middle of a legacy system transfer taking information out of one 20 year old legacy system in oracle and putting it in another 20 year old legacy system. The vendor wants us to write queries in an mde he has defined himself that he doesn't want additional tables added or linked to it. The mdb actually wipes all information in tables prior to some kind of validation so we have to define everything in queries and consecutively run them.

The new database has code fields all over the place as well and I might want to loop through the other sql to find the code fields and set up a complete variety of queries. Which I can potentially do with this method.

Certainly a strange situation.
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 13:20
Joined
Oct 17, 2014
Messages
3,506
As if you don't have enough problems there might be a limit to the number of nested IIFs you can put in a query. If you google this topic you find opinions ranging from a limit of 7 IIFs to the 1024 character limit of the query design grid. When I experimented with this I found the limit to be 14 which I admit is really strange. I didn't do any testing to see if the limit I found held when the SQL was in code rather than in a querydef.

Good luck with your project
 

Lightwave

Ad astra
Local time
Today, 20:20
Joined
Sep 27, 2004
Messages
1,521
Yes you are correct looks like there is a limit on the number of nested IFs that can exist within an SQL statement - whether stored as SQL or within the query designer. I haven't done full testing but 14 works and 27 doesn't.

We are just going to have to use a linked table and update from that. I guess we might be able to have some code that chunks up the updates in to multiples of 14 (or whatever the maximum nested iff limit is) Thanks for your points
 
Last edited:

Users who are viewing this thread

Top Bottom