replace null values

[maggie]

Is there a way to replace all null values by zeroes in a table?
I need to find all null values in all fields for several tables, the UPDATE
query only allows me to replace one field at a time, and I have hundreds of
fields to search and replace.

Thanks,

 

[Dale Fye]

Why would you want to replace the null values? Null values have an
appropriate place in most tables. I will assume that you know what you are
doing and that you reaaly do want to change these values from Null to zero.
You can accomplish this in a query? You could write a single query that
looks something like the following:

UPDATE yourTable SET [Field1]= NZ([Field1], 0), [Field2] = NZ([Field2],0),
[Field3] = NZ([Field3])

To do this, open a new query, pull your table into the query grid and select
all the numeric fields you want to update. Then select Query -> Update from
the database menu. In the update row of each column insert insert a call to
the NZ () function and pass it two parameters, the first parameter is the
field name corresponding to the column of the grid you are in, the second
parameter should be zero. Technically, you can leave the zero off, but I
normally explicitly tell Access what I want to put in there.

*Note: Make sure not to do this on non-numeric fields.

Another way would be to write some code to loop through all of the fields in
your table, the instructions below will help you accomplish this, and will
only update the numeric fields. Before you run this code, copy your
database and save it so you can go back to it if you decide that this is not
really what you want.

1. Open your Access database, press CTRL-G to open the code window.
2. From the Main Menu, Select Tools -> References, then scroll down the
listbox and put a check mark in the box next to Microsoft DAO 3.6 Object
Library. Then click OK
3. In the project window, right click and select Insert ->Module

4. Cut and past the subroutine below into the code window.
5. In the immediate window (if this is not visible select View->Immediate
from Visual Basic Editor main menu), type the following line and hit return.
It should give you a list of the fields where the values were changed to
zero.

CALL NullToZeroFields("yourTable")
_____________________________________________
Public Sub NullToZeroFields(TableName As String)

Dim strsql As String
Dim strfieldname As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field

strsql = "SELECT * FROM [" & TableName & "] WHERE TRUE = FALSE"
Set rs = CurrentDb.OpenRecordset(strsql)
For Each fld In rs.Fields

Select Case fld.Type
Case 3, 4, 6, 7, 19, 20 'These are numeric field types
strsql = "UPDATE [" & TableName & "] SET [" & fld.Name & "]
= 0 WHERE [" & fld.Name & "] IS NULL"
Debug.Print fld.Name, fld.Type, strsql
CurrentDb.Execute strsql
Case Else 'Other data types
'do nothing
End Select

Next
rs.Close
Set rs = Nothing

End Sub

HTH
Dale

 

[John Vinson]

Is there a way to replace all null values by zeroes in a table?
I need to find all null values in all fields for several tables, the UPDATE
query only allows me to replace one field at a time, and I have hundreds of
fields to search and replace.

Thanks,

I agree with Dale that it's probably not necessary to do this... but
if you do wish to do so, you only need one query per table. Update
each field to

NZ([fieldname])

This will update the field to 0 if it is NULL, and to its current
value (i.e. leaving it unchanged) if it isn't.

John W. Vinson[MVP]

 

[maggie]

Thanks so much for the help.

The reason I'm doing this is because there's a lot of graphs and reports
generated from the database every month. The database is fairly large, it's
for a big plant, and various information is gathered and entered daily. I've
set the default value as zero for all those fields, but the data entry person
is not familiar with computer at all, and with the amount of data everyday,
she occasionally leaves null values. So I figured instead of dealing with
the null problems every time we generate new queries, it's better to replace
them in one query. However, usually the majority of entries are zero, so I'm
not sure if that's the best solution.

Why would you want to replace the null values? Null values have an
appropriate place in most tables. I will assume that you know what you are
doing and that you reaaly do want to change these values from Null to zero.
You can accomplish this in a query? You could write a single query that
looks something like the following:

UPDATE yourTable SET [Field1]= NZ([Field1], 0), [Field2] = NZ([Field2],0),
[Field3] = NZ([Field3])

To do this, open a new query, pull your table into the query grid and select
all the numeric fields you want to update. Then select Query -> Update from
the database menu. In the update row of each column insert insert a call to
the NZ () function and pass it two parameters, the first parameter is the
field name corresponding to the column of the grid you are in, the second
parameter should be zero. Technically, you can leave the zero off, but I
normally explicitly tell Access what I want to put in there.

*Note: Make sure not to do this on non-numeric fields.

Another way would be to write some code to loop through all of the fields in
your table, the instructions below will help you accomplish this, and will
only update the numeric fields. Before you run this code, copy your
database and save it so you can go back to it if you decide that this is not
really what you want.

1. Open your Access database, press CTRL-G to open the code window.
2. From the Main Menu, Select Tools -> References, then scroll down the
listbox and put a check mark in the box next to Microsoft DAO 3.6 Object
Library. Then click OK
3. In the project window, right click and select Insert ->Module

4. Cut and past the subroutine below into the code window.
5. In the immediate window (if this is not visible select View->Immediate
from Visual Basic Editor main menu), type the following line and hit return.
It should give you a list of the fields where the values were changed to
zero.

CALL NullToZeroFields("yourTable")
_____________________________________________
Public Sub NullToZeroFields(TableName As String)

Dim strsql As String
Dim strfieldname As String
Dim rs As DAO.Recordset
Dim fld As DAO.Field

strsql = "SELECT * FROM [" & TableName & "] WHERE TRUE = FALSE"
Set rs = CurrentDb.OpenRecordset(strsql)
For Each fld In rs.Fields

Select Case fld.Type
Case 3, 4, 6, 7, 19, 20 'These are numeric field types
strsql = "UPDATE [" & TableName & "] SET [" & fld.Name & "]
= 0 WHERE [" & fld.Name & "] IS NULL"
Debug.Print fld.Name, fld.Type, strsql
CurrentDb.Execute strsql
Case Else 'Other data types
'do nothing
End Select

Next
rs.Close
Set rs = Nothing

End Sub

HTH
Dale

Is there a way to replace all null values by zeroes in a table?
I need to find all null values in all fields for several tables, the
UPDATE
query only allows me to replace one field at a time, and I have hundreds
of
fields to search and replace.

Thanks,

 

[Claude]

Maggie, the replies I have seen here seem a little daunting. I have had rhe
same problem. What I did: In query design View, Pull in the table to be
updated.. If there are many tables, pull them all in and set as Select Query.
Then, pull in all the fields from the various tables which you need updated.
Visually check that these are the fields containg blanks, by running the
query. Then return to Design View, and in the Criteria section enter "Is
Null". Now run the query again - this time it should return all fields
containing blanks only. (You now know you have the culprits). Return to
design view. At top of your screen, pick Query, and change it to Update. Once
this done, you will see your design grid look a bit different - it will have
an "Update to" field added. In each one of these fields, enter "0" (as is
written, ie, the 0 must be enclosed in quotation marks). Now Click Run. It
should ask you something like "Are you really sure you wanna do this? Could
we dissuade you, sell you something, annoy you? Answer with "Just shut up and
run. It should now have updated all the affected fields to 0! Best of luck.