#NUM! error

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

I get #NUM! error with this text control expression. How is that
possible?

=(Nz([TotalPayed],0)/Nz([TotalBilled],1))

The source fields are also calculated and can have nulls that I've Nz'd.



Here are those fields:


TotalPayed

=(Nz([CPT1PaymentTotal],0)+Nz([CPT2PaymentTotal],0)+Nz([CPT3PaymentTotal],0)
+Nz([CPT4PaymentTotal],0)+Nz([CPT5PaymentTotal],0)+Nz([CPT6PaymentTotal],0))

TotalBilled

=(Nz(([CPT1UnitCharge]*[CPT1Units]),0)+Nz(([CPT2UnitCharge]*[CPT2Units]),0)+
Nz(([CPT3UnitCharge]*[CPT3Units]),0)+Nz(([CPT4UnitCharge]*[CPT4Units]),0)+Nz
(([CPT5UnitCharge]*[CPT5Units]),0)+Nz(([CPT6UnitCharge]*[CPT6Units]),0))
 
Nick Mirro said:
I get #NUM! error with this text control expression. How is that
possible?

=(Nz([TotalPayed],0)/Nz([TotalBilled],1))

The source fields are also calculated and can have nulls that I've
Nz'd.



Here are those fields:


TotalPayed

=(Nz([CPT1PaymentTotal],0)+Nz([CPT2PaymentTotal],0)+Nz([CPT3PaymentTotal
],0)+Nz([CPT4PaymentTotal],0)+Nz([CPT5PaymentTotal],0)+Nz([CPT6PaymentTotal]
,0))
TotalBilled
Click to expand...
=(Nz(([CPT1UnitCharge]*[CPT1Units]),0)+Nz(([CPT2UnitCharge]*[CPT2Units])
,0)+Nz(([CPT3UnitCharge]*[CPT3Units]),0)+Nz(([CPT4UnitCharge]*[CPT4Units]),0
)+Nz
Click to expand...
(([CPT5UnitCharge]*[CPT5Units]),0)+Nz(([CPT6UnitCharge]*[CPT6Units]),0))

Do you get the error regardless of what TotalPayed and TotalBilled are?
If TotalBilled is zero, your expression will be dividing by zero, which
is a no-no.
 
Unfortunately, Access doesn't support a calculated field
on a calculated field. Resolution: put the TotalPayed
and TotalBilled in your query underlying the form. Then
you'd be able to use your expression below.

Regards,
Jen
-----Original Message-----
I get #NUM! error with this text control expression. How is that
possible?

=(Nz([TotalPayed],0)/Nz([TotalBilled],1))

The source fields are also calculated and can have nulls that I've Nz'd.



Here are those fields:


TotalPayed

=(Nz([CPT1PaymentTotal],0)+Nz([CPT2PaymentTotal],0)+Nz ([CPT3PaymentTotal],0)
+Nz([CPT4PaymentTotal],0)+Nz([CPT5PaymentTotal],0)+Nz ([CPT6PaymentTotal],0))

TotalBilled

=(Nz(([CPT1UnitCharge]*[CPT1Units]),0)+Nz
Click to expand...
(([CPT2UnitCharge]*[CPT2Units]),0)+
Nz(([CPT3UnitCharge]*[CPT3Units]),0)+Nz(([CPT4UnitCharge]* [CPT4Units]),0)+Nz
(([CPT5UnitCharge]*[CPT5Units]),0)+Nz(([CPT6UnitCharge]*
Click to expand...
[CPT6Units]),0))


.
Click to expand...
 
Nick Mirro said:
I also get it with:

=(Nz([TotalPayed],1)/Nz([TotalBilled],1))

How can I get this field to ignore a zero value from the source
field. When the data is zero (not null), I get this error. I cannot
alter the expression in the source field without causing incorrect
values. Is there another way around this?
Click to expand...

Your Nz() expressions don't seem to me to make sense. What do you want
the expression to return if:

TotalPayed = 0 ?
TotalBilled = 0 ?

Normally, I'd expect you to want 0 returned if TotalPayed = 0. Since
division by zero is undefined, it's not clear what you want to see if
TotalBilled = 0. You *could* force that to zero, using an expression
like

=IIf([TotalBilled]=0, 0, [TotalPayed]/[TotalBilled])

It looks as though you've already ensured that neither TotalPayed nor
TotalBilled will be Null, so I think you can drop the Nz() function
calls from this expression.
 
Jen said:
Unfortunately, Access doesn't support a calculated field
on a calculated field. Resolution: put the TotalPayed
and TotalBilled in your query underlying the form. Then
you'd be able to use your expression below.
Click to expand...

You're mistaken, Jen. It won't support that for aggregate functions
like Sum(), but it will for expressions like these, and I think it's the
division by zero that is giving the error. Nevertheless, your
suggestion to put TotalPayed and TotalBilled into the query as
calculated fields is a good one.
 
Doesn't make sense to me either now that I look at it. Your expression
worked perfectly. Thanks.


Dirk Goldgar said:
Nick Mirro said:
I also get it with:

=(Nz([TotalPayed],1)/Nz([TotalBilled],1))

How can I get this field to ignore a zero value from the source
field. When the data is zero (not null), I get this error. I cannot
alter the expression in the source field without causing incorrect
values. Is there another way around this?
Click to expand...

Your Nz() expressions don't seem to me to make sense. What do you want
the expression to return if:

TotalPayed = 0 ?
TotalBilled = 0 ?

Normally, I'd expect you to want 0 returned if TotalPayed = 0. Since
division by zero is undefined, it's not clear what you want to see if
TotalBilled = 0. You *could* force that to zero, using an expression
like

=IIf([TotalBilled]=0, 0, [TotalPayed]/[TotalBilled])

It looks as though you've already ensured that neither TotalPayed nor
TotalBilled will be Null, so I think you can drop the Nz() function
calls from this expression.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top