VB Code to update Multiple Table Fields (1 Viewer)

[jfgambit]

Afternoon Folks...

I have an export that comes out of Siebel that we import into an Access 2000 Db. I import the csv file into a temp table to do some separation into two tables (this Normalizes the information in the database). The problem that I have is that when there is a NULL value in a field in Siebel the export has the word "NULL" in the field. There are approximately 20 fields and any one of these could have the word "NULL" in them.

Does anyone have code that will loop through each field in the table and replace the text word "NULL" with an actual Null value?

I could do it with Docmd.RUNSQL "blah, blah, blah...", but it seems kind of ridiculous to write 20 Docmd statements and if we add additional fields to the extract then I have to add more DoCmd lines to the code.

 

[Surjer]

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Mytable")

Do While Not rs.EOF
Dim i As Integer
For i = 0 To (rs.Fields.Count - 1)
If rs(i).Value = "Null" Then
rs.Edit
rs(i).Value = ""
rs.Update
End If
next i
rs.MoveNext
Loop
End Sub

 

[jfgambit]

Thanks Jerry...had to change one line...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from Mytable")

Do While Not rs.EOF
Dim i As Integer
For i = 0 To (rs.Fields.Count - 1)
If rs(i).Value = "Null" Then
rs.Edit
rs(i).Value = Null
rs.Update
End If
next i
rs.MoveNext
Loop
End Sub

Again...thanks for the quick response!!