Eliminate #Error from Query Results??

[Guest]

Good Day all,

I have the following calculation in my qryinvcalc, and it works fine except
that if there is a null value I get an "#Error" within my query results.

PercentofTotalM2Shipped: Round([ttlm2Shipped]/[TotalM2Shipped],4)

I have search and found some post about using "NZ", I am unsure how to apply
that to my equation or what exactly this does?

Thanks in advance..

Brook

 

[Ken Snell [MVP]]

Nz is a VBA function that allows you to use a substitute value for a Null
value. In your example, either ttlm2Shipped or TotalM2Shipped could have a
Null value. For purposes of display, I'll show you many ways to use the Nz
function in this setup.

' replace Null value in numerator with a zero
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped],0) / [TotalM2Shipped],4)

' replace Null value in numerator with a zero and Null value in denominator
with a one
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped],0) / Nz([TotalM2Shipped],
1) ,4)

' replace Null value from division result with a zero
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped] / [TotalM2Shipped], 0) ,4)

etc. etc.

 

[Guest]

Hello ken,

Thanks for the response..

I tried your suggestions but was still getting an #Error in my qry results.

So I found some information and went the route of :

PercentofTotalM2Shipped:
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4))

But thanks for the help!

Brook

Nz is a VBA function that allows you to use a substitute value for a Null
value. In your example, either ttlm2Shipped or TotalM2Shipped could have a
Null value. For purposes of display, I'll show you many ways to use the Nz
function in this setup.

' replace Null value in numerator with a zero
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped],0) / [TotalM2Shipped],4)

' replace Null value in numerator with a zero and Null value in denominator
with a one
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped],0) / Nz([TotalM2Shipped],
1) ,4)

' replace Null value from division result with a zero
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped] / [TotalM2Shipped], 0) ,4)

etc. etc.

--

Ken Snell
<MS ACCESS MVP>

Good Day all,

I have the following calculation in my qryinvcalc, and it works fine
except
that if there is a null value I get an "#Error" within my query results.

PercentofTotalM2Shipped: Round([ttlm2Shipped]/[TotalM2Shipped],4)

I have search and found some post about using "NZ", I am unsure how to
apply
that to my equation or what exactly this does?

Thanks in advance..

Brook

 

[Ken Snell [MVP]]

OK - that is a different error than having a Null value in a field; that is
because the denominator is zero.

Glad you got a solution.

--

Ken Snell
<MS ACCESS MVP>

Hello ken,

Thanks for the response..

I tried your suggestions but was still getting an #Error in my qry
results.

So I found some information and went the route of :

PercentofTotalM2Shipped:
IIf([ttlm2shipped]=0,0,Round([ttlm2Shipped]/[TotalM2Shipped],4))

But thanks for the help!

Brook

Nz is a VBA function that allows you to use a substitute value for a Null
value. In your example, either ttlm2Shipped or TotalM2Shipped could have
a
Null value. For purposes of display, I'll show you many ways to use the
Nz
function in this setup.

' replace Null value in numerator with a zero
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped],0) / [TotalM2Shipped],4)

' replace Null value in numerator with a zero and Null value in
denominator
with a one
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped],0) /
Nz([TotalM2Shipped],
1) ,4)

' replace Null value from division result with a zero
PercentofTotalM2Shipped: Round(Nz([ttlm2Shipped] / [TotalM2Shipped], 0)
,4)

etc. etc.

--

Ken Snell
<MS ACCESS MVP>

Good Day all,

I have the following calculation in my qryinvcalc, and it works fine
except
that if there is a null value I get an "#Error" within my query
results.

PercentofTotalM2Shipped: Round([ttlm2Shipped]/[TotalM2Shipped],4)

I have search and found some post about using "NZ", I am unsure how to
apply
that to my equation or what exactly this does?

Thanks in advance..

Brook