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
In my example database this creates the following text file
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
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: