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! :)

[NeoPa]

Doma:
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.

Doma, there are certain things that are location specific. One is that in some European countries (I'm not sure which) the semi-colon (;) is used in place of the comma (,) as compared to the English usage which I am more familar with of course. I'm surprised this reflects within SQL (ControlSource is essentially SQL) in the way that you describe, but understand this is not a global setting. It varies between countries, just as some European countries use the dot (.) as the thousands separator and comma (,) to separate the decimals in a number (Whereas they work the other way around in England and USA).

Doma:
I think that the problem was in the clearing procedure which would set the text controls to "" instead of to a NULL.

I'm curious to understand what you are saying. I wasn't aware that controls ever could handle empty strings. What does the clearing procedure mean in this context?

Doma:
It wasn't the val()

Are you saying that using Val() didn't fix the problem? I'd be very surprised by this.

[doma23]

Regarding location specific settings. OK, I didn't know that. I knew about the dot and comma differences, but about semi-colons and commas in VBA and Access I did not.
Like you said, it's even more surprising that it would reflect in SQL (be it in controlsource or vba sql string). Weird...maybe functions are programmed to work differently.

By clearing procedure, I've meant the sub that was called when somebody would press CLEAR command button on a form. That sub would put the value of text controls to empty string ("").
This was the problem actually. What is strange is that even if I wouldn't press clear button at all, it would still give an error in calculation.
After I changed the procedure to set the text control values to null instead of empty strings, it worked.
Maybe the reason might be that I don't use "Compile on demand" setting, so I guess it actually compiles everything each time some procedure is executed.

Regarding Val(), I'm saying that the main problem I've had wasn't Val(). Calculation didn't work at all. Actually, on some occasions it did, and when it did worked I've noticed that it's concatenating the values and not summing them, but I've solved that by formating the text control in control properties as number. I'm sure that the Val() would work just as fine.

PS. How can I quote someone in my reply?

[NeoPa]

As you'll see from your other current thread Doma, Locale settings in Format() (which certainly is location-aware) can play a big (and somewhat hard to predict) part. I'm no expert as I work in English in GB and I'm using very close to default in most cases. My only experience (other than the SQL date part order) is from items reported by people working in other countries like yourself. It certainly seems that you also have dot (.) and slash (/) confusions with date separators too I'm afraid.

It seems controls can hold the empty string after all. I'd never seen and and had assumed it was never the case, but after your comments, and some of my own testing, I can confirm it is able to store such a value. Normal usage would never create this value (except maybe in some unusual circumstances), as it would always be set to Null, but that's not the same as a control being unable to store an empty string. I would however, recommend setting the controls to Null to clear them generally. They will be Null anyway if deleted manually and it's no great idea to give yourself extra work to do to handle multiple scenarios where one will do.

There are various ways, as you've found, to indicate that you'd like the value treated as numeric rather than as string. Val() is probably the most common, but anything that ensures it's recognised as numeric does the job.

Doma:
PS. How can I quote someone in my reply?

I have routines I've set up that help me formulate my replies, but essentially I use the highlight tag for the red. You can find all the details in BB Code.