Nulls and Visual Basic

[Vern]

I have a pretty big (3012 x 5) Table with many nulls/empty/blanks(probably a couple thousand). I would like, in Visual Basic, to replace these by a zero . I have tried using If IsEmpty syntax, If IsNull syntax, If Is Null, if = "" syntax and nothing seems to work. Either I get an error or the computer flows thru (using debug) the code and does not do anything.
Maybe I will have to use an update query but I'd rather the VB as I would like a Click event to do it all

Can anyone help? I feel so stupid not being able to do this apparently simple process.

Vern

 

[vbaInet]

Is that 3012 rows of data and 5 columns?

 

[mdlueck]

I would like, in Visual Basic, to replace these by a zero.

And update the NULL value to be a 0 value in the table?

Or only within VBA variables to have the value be 0 and the NULL's to remain in the actual table?

or or or...

 

[Vern]

Is that 3012 rows of data and 5 columns?

Yes field count is 3012x5

 

[Vern]

And update the NULL value to be a 0 value in the table?

Or only within VBA variables to have the value be 0 and the NULL's to remain in the actual table?

or or or...

I would like all the nulls in the table to be replaced by a zero(0)

 

[vbaInet]

Yes field count is 3012x5

That doesn't answer my question. How many fields do you have? You cannot have 3012 fields.

 

[elliotgr]

Try this
strColumn is the number of columns in your table.
In this case I tested with 2 (0 To 1) change to your table size

Sub Update_To_Zero()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableNameHere")
With rst
If .RecordCount <> 0 Then
    .MoveFirst
    Do Until .EOF
        For strColumn = 0 To 1
            If IsNull(rst.Fields(strColumn )) Then
                .Edit
                rst.Fields(strColumn ) = 0
                 .Update
            ElseIf rst.Fields(strColumn ) = "" Then
                .Edit
                 rst.Fields(strColumn ) = 0
                 .Update
            End If
         Next
        .MoveNext
        Loop
End If
End With
rst.close
Set rst = nothing
db.close
Set db = nothing
 
End Sub

 

[vbaInet]

@elliotgr: The point here is not to update the fields because the OP shouldn't need to do that. He can use the Nz() function or even set a Default Value of 0. We're trying to determine the best route to take on this after the OP confirms how many rows of data and how many columns are in play here.

 

[elliotgr]

I had a similar problem when importing data. Instead of creating a table upfront in VBA and setting the properties and default values, I just imported the data, and then updated it.
Maybe this is the problem as well?

 

[vbaInet]

Even in that case you won't use a Recordset, you would use SQL. Let's wait and get more clarification from the OP.

 

[Vern]

There are 3012 rows and 30 columns (of which I am only interested in the nulls in 5 of them)

 

[vbaInet]

Ok. Do you know about the Nz() function?

http://www.techonthenet.com/access/functions/advanced/nz.php

You can also use the Format property too or like already mentioned, set the Default Value property of the fields to 0 as well.

 

[Pat Hartman]

I'd think long and hard before replacing all the nulls with zeros. Zero has a meaning. In the instance of grades for example. Zero means the student took the test and received a gooseegg for his efforts. Null means that the result is not yet reported or he didn't take the test. Changing the nulls to 0 will also affect aggregate functions if you do any. For example, the average of 3, null, 3 is 3. The average of 3, 0, 3 is 2.