Loop through fields and update nulls to zero's

[swedbera]

How would you loop through fields and update null values to zero's in vba?
If it was only one field, I would run a query or write an SQL statement.

I've seen many posts similar, but I can't get it to work. Here is my code.

With rst
Do Until .EOF
For I = 0 To rst.Fields.Count - 1
If rst.Fields(I).Value = Null Then
rst.Fields(I).Value = 0
rst.Update
End If
Next I
.MoveNext
Loop
.Close
End With

Please help me with this

Arlene

 

[Allen Browne]

The rst.Edit bit is missing.

There are some more fundamental questions though. Why do you need the nulls
as zero in all fields of all records? If it is just that you are having
difficulty handling nulls, then Nz() might help. It really is essential to
take the time to handle nulls if you are working with databases, as they are
so crucial, though they can seem quirky. See:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

Further, assigning a zero to all fields won't work if you have any text
fields as most tables do. If you do have all numeric columns and lots of
them, it sounds like a spreadsheet design, and the best solution might be to
create a related table with one column to hold all the numbers, instead of
lots of repeating columns in the one table.

 

[George Nicholson]

but, if you really, really want to do this, regardless of Allen's good
advice, then:should probably be restated as
If IsNull(rst.Fields(I).Value) Then

AFAIK, "= Null" is never correct usage.

HTH,

 

[swedbera]

I had a few records that didn't produce a total where some of the fields used
in the calculation were null. I changed the formulas (added nz), but I am
still getting another error(#Div/0!) for some of the other calculations.

Arlene

 

[Klatuu]

George is correct, nothing ever equals Null.
Your divide bi 0 problem is easily solved:

X = Y/Z should be: X = Y/IIf(IsNull(Z,0)=0,1,Z)

Also, in your table looping, you should be checking the field type because,
as Allen stated, you don't want to do this to Text fields. Also, notice I
moved the Update. There is no need to update each time you change a field.
You are only degrading performance and increasing network traffic. And, I
removed the rst in front of all the references to it. When you are using the
With...End With contstruct, you don't need it. That is what With...End With
is for. And lastly, I removed the .Value. It is also not needed. .Value is
the default.

With rst
Do Until .EOF
.Edit
For I = 0 To .Fields.Count - 1
If IsNull(.Fields(I)) And .Fields(I).Type <> 10 Then
.Fields(I) = 0
End If
Next I
.Update
.MoveNext
Loop
.Close
End With

 

[David C. Holley]

Ah ha! The bigger picture unfolds. If the field in question should
contain a value, then YES it should always contain at least a 0. If
you're doing calculations on the field, I would use a condition if IIF()
to test if the whatever value (dividend?) is zero to avoid the Div/0 error.

 

[Klatuu]

In regard to my previous post. I noticed I am also doing unnecessary
updates. We don't need to update if no changes have been made to the row.
This version fixed that:

With rst
Do Until .EOF
.Edit
For I = 0 To .Fields.Count - 1
If IsNull(.Fields(I)) And .Fields(I).Type <> 10 Then
.Fields(I) = 0
blnDoUpdate = True
End If
Next I
If blnDoUpdate Then
.Update
blnDoUpdate = False
End If
.MoveNext
Loop
.Close
End With

 

[swedbera]

First, Thanks to all of you for your help!

I updated my code and it works great - no more nulls. I wouldn't care if
there were nulls, but some people like to see a zero when they are expecting
to see a number. If they don't, they assume that that particular field was
overlooked during data entry. Also, I hadn't even thought about text fields
and I do have other fields that are text, but contain numbers (DeptId and
AccountNumber). If they are null, I would want them to stay that way. Thank
you for pointing that out to me - I'm still learning.

My question now is, do I not need the nz function? Should I change all of
my calculations to use the IIF and IsNull functions? The nz function works
great for text fields, but it doesn't seem like I should have been using it
here.

Arlene