I cannot find the typo...please help wih this Nz statement

[lynthel]

I am adding 4 fields together that contain either 0 or a currency value...I want to show a "0" in the field if the value is Null or ""...Nz is nested in each of the 4 fields that are being adding so each field is set to "0" if there are any blanks or nulls.


I have included the statement for one of the other fields where the Nz statement works great...I can not get the "sum of the other fields" to work Please Help!

Working statement in one of the fields being added

=IIf(Nz([sage_sd_schlrshp],"")="","0","& [sage_sd_schlrshp]")

this is the "sum of the other fields" statement

= IIf(Nz(=([sage_sd_schlrshp]+[sage_vf_schlrshp]+[vf_private_donor]+[Other]),"")="","0",=([sage_sd_schlrshp]+ [sage_vf_schlrshp]+ [vf_private_donor]+[Other]))

I have even tried this statement

= IIf(Nz(=([sage_sd_schlrshp]+[sage_vf_schlrshp]+[vf_private_donor]+[Other]),"")="","0","& =([sage_sd_schlrshp]+ [sage_vf_schlrshp]+ [vf_private_donor]+[Other])")

[June7]

Why do you have those 2 extra = signs?

Both expressions reference the same field, but neither expression contributes to the result of the other.

[lynthel]

Why do you have those 2 extra = signs?

Both expressions reference the same field, but neither expression contributes to the result of the other.

Taking out the extra "=" did the trick...as for the statement...I want Access to either return the sum of the other fields or return a 0....when I tried just adding the other fields, I would get a #name? error showing on the form. I think it might have had to do with setting the other fields to "0" (a string) if no value was put in those fields. Does Access have a "Convert.ToInt" function?

[June7]

CInt, CDbl, CLng

However, those all fail on either empty string or null.

Val() will convert "" to 0 but fails on null.

[lynthel]

Thank you very much June7...now on to the next question (which I am sure I will see and look forward to your help)

[John_G]

Hi -

You don't really need the IIf part - the Nz is sort of an abbreviated IIf in itself. All you need is to add the 4 fields, each within its own Nz:

=Nz([sage_sd_schlrshp],0) + nz([sage_vf_schlrshp],0) + nz([vf_private_donor],0) + nz([Other],0)

Set the default value for each of these fields to 0 - that way you know the Nz will work. When entering data into a form textbox, all-blanks is treated the same as no data - Null.

HTH

John