#Num!

  • Thread starter Thread starter Claudette Hennessy
  • Start date Start date
C

Claudette Hennessy

For instance, a calculated field is set to [Amount]/[UnitPrice]. This
results in #Num! on some records. Am looking for an expression that results
in 0 if either [Amount] or [Unit Price] is null.
Claudette Hennessy
 
This works if the fields are null, but results in #Name? is the fields are
populated.
Ofer said:
iif(isnull([UnitPrice]),0,nz([Amount],0)/[UnitPrice])

You use the NZ function to replace the null with another value, we can do
it
to the second number, in that case will get another error "devision by
zero",
so we'll use the iif for the second value and the nz for the first value.

Claudette Hennessy said:
For instance, a calculated field is set to [Amount]/[UnitPrice]. This
results in #Num! on some records. Am looking for an expression that
results
in 0 if either [Amount] or [Unit Price] is null.
Claudette Hennessy
Click to expand...
Click to expand...
 
This results in #Name?

Wayne Morgan said:
=Nz(IIf([UnitPrice]=0, 0, [Amount]/[UnitPrice]), 0)

You can't divide by 0 without getting an error, but with either value
Null, the result will be Null. The IIf solves the divide by zero problem,
then the Nz handles the Null result.

--
Wayne Morgan
MS Access MVP


Claudette Hennessy said:
For instance, a calculated field is set to [Amount]/[UnitPrice]. This
results in #Num! on some records. Am looking for an expression that
results in 0 if either [Amount] or [Unit Price] is null.
Claudette Hennessy
Click to expand...
Click to expand...
 
Then I would also suspect a references error. To see the references, open
the code editor (Alt+F11) and go to Tools|References. For information on
checking these for a problem and to fix them, check here.

http://www.allenbrowne.com/ser-38.html

Another possibility that will cause this is if the control on the form and
the field it is bound to each have the same name. For example, if the Amount
field is bound to a textbox that also has the name Amount. If so, try
changing the textbox's name to txtAmount. Do this for UnitPrice also.

--
Wayne Morgan
MS Access MVP


Claudette Hennessy said:
This results in #Name?

Wayne Morgan said:
=Nz(IIf([UnitPrice]=0, 0, [Amount]/[UnitPrice]), 0)

You can't divide by 0 without getting an error, but with either value
Null, the result will be Null. The IIf solves the divide by zero problem,
then the Nz handles the Null result.
Click to expand...
Click to expand...
 
This works! after I changed the textbox names to txtAmount and txtUnitPrice.
thank you very much. Seeing #Num! in a calculated field for new records has
bugged me for a long time.
Claudette Hennessy
Wayne Morgan said:
=Nz(IIf([UnitPrice]=0, 0, [Amount]/[UnitPrice]), 0)

You can't divide by 0 without getting an error, but with either value
Null, the result will be Null. The IIf solves the divide by zero problem,
then the Nz handles the Null result.

--
Wayne Morgan
MS Access MVP


Claudette Hennessy said:
For instance, a calculated field is set to [Amount]/[UnitPrice]. This
results in #Num! on some records. Am looking for an expression that
results in 0 if either [Amount] or [Unit Price] is null.
Claudette Hennessy
Click to expand...
Click to expand...
 
Back
Top