Update Null to Zero value

[ijswalker]

Hi,

I am in the process of building a database in Access 2003.
I am trying to update a table to replace all of the Null values in number formatted fields with zero. I thought about updating one column at a time but that does not seem to be efficient.

Does anyone know of a way to do all columns at once considering there are about 10 columns? I tried this using the query wizard but not sure how to tell it to update only those fields that have null values with zero.

Your help would be appreciated.

Thanks

Ian

 

[dkinley]

You can use an update query (update with 0) with the criteria "Is Null". This will put 0 in all of those null fields.

-dK

 

[boblarson]

You'll need to do an update query which can have all 10 fields in it and then set the value to update to 0 and Is Null in each of the columns, but if you want the nulls to be updated in each you will need to make sure the Is Null is on separate lines in your query grid. It actually may be simpler just to do it one column at a time and just change the column name while in the query by example grid after each one.

 

[ijswalker]

Thanks, I think both will work. However, I am getting an issue with the datatypes.

In my destination field the field are number fields with Field Size "Double" and Format "Standard"

I am using 0 for the "Update to" value and where the criteria "IsNull"

Also tried putting 0.00 as the replacement value and also tried putting those in double quotes. Still no luck

Can you help?

Ian

 

[boblarson]

In my destination field the field are number fields with Field Size "Double" and Format "Standard"

I am using 0 for the "Update to" value and where the criteria "IsNull"

That should be 0 for the update to value and the criteria is Is Null without quotes and with a space.

 

[datAdrenaline]

Well ... I hate to say this, but I would use caution with the two suggested methods simply because it seems (from the way I read it) that you will overwrite an existing value if you "OR" your Is Null criteria ... for example, from the way I read the other suggestions, you will end up with an SQL statement like this ...

UPDATE tblSomeTable
SET Field1 = 0,    
    Field2 = 0,
    Field3 = 0,
    Field4 = 0,
    Field5 = 0,
    Field6 = 0,
    Field7 = 0,
    Field8 = 0,
    Field9 = 0,
    Field10 = 0
WHERE Field1 Is Null Or Field2 Is Null Or Field3 Is Null Or Field4 Is Null
       Field5 Is Null Or Field6 Is Null Or Field7 Is Null Or Field8 Is Null
       Field9 Is Null Or Field10 Is Null

With the above, all your fields would update to 0 IF ANY field in the row is Null ... which is probably not what you want.

So ... I propose the use an IIf() in your Update expression, plus you can capitalize on the Law of Propogating Nulls and still only update the rows that have at least of the fields set to Null.

UPDATE tblSomeTable
SET Field1 = IIf(Field1 Is Null, 0, Field1),    
    Field2 = IIf(Field1 Is Null, 0, Field2),
    Field3 = IIf(Field1 Is Null, 0, Field3),
    Field4 = IIf(Field1 Is Null, 0, Field4),
    Field5 = IIf(Field1 Is Null, 0, Field5),
    Field6 = IIf(Field1 Is Null, 0, Field6),
    Field7 = IIf(Field1 Is Null, 0, Field7),
    Field8 = IIf(Field1 Is Null, 0, Field8),
    Field9 = IIf(Field1 Is Null, 0, Field9),
    Field10 = IIf(Field1 Is Null, 0, Field10)
WHERE (Field1 + Field2 + Field3 + Field4 + Field5 +
        Field6 + Field7 + Field8 + Field9 + Field10) Is Null

Hope that helps!! ...

 

[ijswalker]

Thanks, Access seems to have a habit of automatically inserting double quotes around the Is Null. Once I manually removed each quote it worked,

Thank you so much. Saved me a lot of time.

Ian

 

[boblarson]

Well ... I hate to say this, but I would use caution with the two suggested methods simply because it seems (from the way I read it) that you will overwrite an existing value if you "OR" your Is Null criteria ...

Oh sure, just be right as usual Brent

 

[datAdrenaline]

>> Oh sure, just be right as usual Brent <<

... well, ya know, were all right ... it just depends on what the OP wants! .. Update all the fields to Zero or not

 

[ijswalker]

Thanks DatAdrenaline,

I like this idea. Will this approach only update those fields in the record that are null.

UPDATE BASE_PROTECTION_DATA
SET Field1 = IIf([FY08 ACTUAL_4TH_QTR] Is Null, 0, ([FY08 ACTUAL_4TH_QTR]),
Field2 = IIf([APPROVED_SENT_TO_AH_4TH QTR] Is Null, 0, [APPROVED_SENT_TO_AH_4TH QTR]),

WHERE (Field1+Field2) Is Null

I have shortened the query for illustration purposes but can't seem to get it to work. I get syntax errors. Can you enlighten me as to what I am doing wrong?

Thanks

Ian

 

[dkinley]

Here ...

SET Field1 = IIf([FY08 ACTUAL_4TH_QTR] Is Null, 0, ([FY08 ACTUAL_4TH_QTR]),

One too many parenthesis ..

SET Field1 = IIf([FY08 ACTUAL_4TH_QTR] Is Null, 0, [FY08 ACTUAL_4TH_QTR]),

-dK

 

[datAdrenaline]

>> Will this approach only update those fields in the record that are null. <<

Yep ...

Also ... I am assuming that your SQL does not have "Field1 + Field2" and you have the actual field names ....

In all honesty, you really don't need anything in the criteria simply because the IIf() takes care of it ... the criteria (ie: The WHERE clause) is just there to speed things up a bit for processing ...

 

[boblarson]

>> Will this approach only update those fields in the record that are null. <<

Yep ...

Also ... I am assuming that your SQL does not have "Field1 + Field2" and you have the actual field names ....

In all honesty, you really don't need anything in the criteria simply because the IIf() takes care of it ... the criteria (ie: The WHERE clause) is just there to speed things up a bit for processing ...

And by now it would have been quicker to just do like I suggested and run the update once for each field to replace nulls with zeros

 

[datAdrenaline]

>> been quicker to just do like I suggested <<

... well maybe! ... but that is not nearly as fun! ... ...

UPDATE tblSomeTable
SET Field1 = 0
WHERE Feild1 Is Null

Run once ... edit ... Run again ... edit .. etc... Could get tiresome if you have to do it more than once ...

....

But ... if you do have to do this more than once, then maybe you should set the Default Value to 0, and mark the field as required at the table level, then no need to run the update repeatedly! ...

 

[ijswalker]

Hi datAdrenaline,

That is awesome. Thanks so much for your help on this one. May have taken a while to get there but will save a lot of time in other situations now that I understand what is happening.

Thanks for everyone's help on this also,

Cheers

Ian