Summing Fields If Not Null

[doma23]

Hi,
I have 5 fields on a form. Field1, Field2, Field3, Field4, SumField
SumField is disabled as it is calculated field based on other four fields.

SumField = Field1 + Field2 + Field3 + Field4

It should only sum the values if they are not null. If one of the fields is null it should skip that field.
Also, null field should stay null, the code must not convert it to zero.

Example:
Field1 = 5
Field2 = null
Field3 = null
Field4 = 10
SumField = 15

I know I can do it with IF code for each field, but there is a lot of this fields and it would complicate whole thing.
Any help appreciated.

[TheSmileyCoder]

SumField = nz(Field1;0) + nz(Field2;0) + nz(Field3,0) + nz(Field4,0)

Nz() will check if the field is null, and if so return the second argument (in this case 0). IF field is not null it will return the field value. Think of Nz as a filter to catch your nulls.

[doma23]

BTW. I've just tried to use nz() function, but it gives me run-time error 13 (Type mismatch).

The code is fixed so that after the update of Field1, Field2, Field3 and Field 4 - Calculating sub is called, where the formula is.

[doma23]

Yeah, I've tried to use nz(), but it gives error, see my previous reply.
I've only entered value in Field1 (5).
When I go to debug, I see whole line highlighted yellow with this values:

SumField = ""
Field1 = 5
Field2 = ""
Field3 = ""
Field4 = ""

[TheSmileyCoder]

Are fields 1 to 4 bound or unbound? and if bound, what type field are they bound to? Text, number or something else?

Please post the code your using in full. I would probably just put the

= nz(Field1;0) + nz(Field2;0) + nz(Field3,0) + nz(Field4,0) 
 

as the controlsource for sumField, without code.

[doma23]

The fields are unbound.
I've tried to put it in controlsource like you said,
it gives me "#error" in calculated field if some of the fields are null.
If all the fields are filled with number, it calculates correctly.
I think that the problem is that it doesn't recognize other fields as null.

I've used this code, notice the commas:

= nz(Field1;0) + nz(Field2;0) + nz(Field3;0) + nz(Field4;0)

----

In the VBA, the code of the sub:

Public Sub CalculateFields()
 
Form_frmMain.txtSumField = Nz(Form_frmMain.txtField1,0) + Nz(Form_frmMain.txtField2,0) + Nz(Form_frmMain.txtField3,0) + Nz(Form_frmMain.txtField4,0)
 
End Sub

[doma23]

Update:
I'm using access 2003 mdb format.
I've created a new accdb database and did a little test, both variants worked perfectly. (VBA and controlsource)
So, I don't know. I know it shouldn't matter, but somehow it does...

[NeoPa]

Let's clarify a bit first. We're not talking about fields here at all are we? These are controls on a form which are not related in any way to fields. Please confirm this statement.

As for Nz(), I believe Smiley included a typo in his usage where he used the semi-colon (;) instead of the comma (,). The comma is required.

Unless I'm missing something, there is no cause for a separate procedure to calculate the value as the calculation is done in the .ControlSource of the SumField control.

The values of unbound controls can never be empty strings (I'm not even sure they would be for a control bound to a text field set up as allowing empty strings but not nulls).

For documentary purposes the .ControlSource of the SumField control should be :

=Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)+Nz([Field4],0)

[NeoPa]

It appears you may need to force this calculation to work as a numeric one by using Val() on the first part. Otherwize it may not realise the value is numeric and treat the whole thing as a string.

Try instead :

=Val(Nz([Field1],0))+Nz([Field2],0)+Nz([Field3],0)+Nz([Field4],0)

PS. This was uncovered by Smiley doing some extra testing on your behalf (See Concatenate (?) versus sum (+) clarification) :)

[doma23]

I've found the problem.
It wasn't the val(), I mean I've noticed this concatenating before, but I've solved it by defining the text control as numeric number in control properties.

I think that the problem was in the clearing procedure which would set the text controls to "" instead of to a NULL.
I've changed that and now it appears to work.
Anyway, thank you guys.

BTW. Control source requires ";" as a separator for nz() function, while VBA requires ",". I'm using Access 2007 and mdb format, but I guess it should be the same for Access 2003 or for the accdb format.

P.S. SmileyOne, lijepi pozdrav! :)