Expressions with blank values

J

Jesse

Hi

I have a form with the following items: Charge, Discount, Payment, Refund.
I have created a field "Balance" which calculates the expression
"Charge-Discount-Payment+Refund".
BUT - it only calculates it when all the Charge/Discount/Payment/Refund
fields are have a number entered. If I leave a field blank, the "Balance"
doesn't calculate. Very often there is no discount or refund.

Is there a way to calculate the "Balance" expression with empty input fields?

Thanks!
Jesse
 
B

Brendan Reynolds

If there's no discount or refund, it would be best to enter a zero in those
fields. If they're Null (no value entered) Access can't really tell whether
this means 'there's no discount or refund' or 'I don't know yet whether
there's going to be a discount or refund'.

Alternatively, If you want your expression to treat Null values as zero, you
can use the NZ function ...

NZ([Charge],0)+NZ([Discount],0)+NZ([Payment],0)+NZ([Refund],0)

See NZ Function in the help file for details.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Top