#Num!

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

Try the Nz function (null to zero function).

Nz([Amount]/[UnitPrice],0)

"Claudette Hennessy" wrote:

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

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" wrote:

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

=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" wrote in message
...
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" wrote in message
...
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" wrote:

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 results in #Name?

"Wayne Morgan" wrote in message
...
=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" wrote in message
...
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

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" wrote in message
...
This results in #Name?

"Wayne Morgan" wrote in
message ...
=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.

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" wrote in message
...
=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" wrote in message
...
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