defaulting a value to zero rather than Null

  • Thread starter Thread starter Ilan Lowbeer
  • Start date Start date
I

Ilan Lowbeer

Hi All

I am sure there is a simple answer to this - but I have
jumped down the usual rabbit holes to no avail.

Your thoughts?

Situation:

I am using queries to conduct a number of calculations...
for example:

Net Invoice = Gross Invoice - Discount1 (may be a number
of different discounts based on the gross invoice)

Net Net Invoice = Net Invoice - Discount2 (may be a number
of different discounts based on the Net Invoice)

Net Net Net Invoice = Net Net invoice - Discount3 (may be
a number of different discounts based on the Net Net
invoice)

So i have a query that calculates the gross invoice and
one that calculates discount 1, I then have another
formula that subtracts one from the other (this is fine).

I then have a query that calculates discount 2 and another
that subtracts it from the Net invoice query (this is fine
too) and a similar thing for the net net net invoice (this
is all good).

However my problem comes when I for some reason don;t have
any discount2's but have discount3's - what is ahppening I
think is that the query goes to make the subtraction at
the Net Net invoice stage and reads discounts2 as Null
rather than zero.

This means that the Net net invoice comes back as null
rather than equalling the net invoice....

this then stuffs up the net net net invoice calculation
because the net net invoice figure is null as well.

thoughts?

Ilan Lowbeer
 
Wrap each field with the Nz function and set the replacement value to zero;
for example:

Net Net Invoice = Nz(Net Invoice, 0) - Nz(Discount2, 0)

If you want to display the result in a specific format other than text (for
example, currency), then add another wrapper function to change the Nz
function's result to that format:

Net Net Invoice = CCur(Nz(Net Invoice, 0)) - CCur(Nz(Discount2, 0))
 
Thanks Ken
-----Original Message-----
Wrap each field with the Nz function and set the replacement value to zero;
for example:

Net Net Invoice = Nz(Net Invoice, 0) - Nz(Discount2, 0)

If you want to display the result in a specific format other than text (for
example, currency), then add another wrapper function to change the Nz
function's result to that format:

Net Net Invoice = CCur(Nz(Net Invoice, 0)) - CCur(Nz (Discount2, 0))

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
 
Back
Top