How do I avoid Nulls when using Sum in report

  • Thread starter Thread starter Derek
  • Start date Start date
D

Derek

Hi

I am using a subreport to total sales. When sales are null
it buggers up my grand total and VAT calculations on main
report. How do I say If Null show Zero

Thanks
 
The NZ ("null to zero") built-in function can be used on each factor in the
calculation to convert it to zero before using it. It is necessary to
convert each factor because _any_ Null in an arithmetic calculation will
cause the calculation to have a Null result.

Larry Linson
Microsoft Access MVP
 
Here's an example of an expression I employ from a crosstab query
....substitute your fieldnames and arithmetic operators:

=Sum(nz([Total of Quantity Delivered])-nz([Total Of Quantity Distributed]))
 
THank you.

I am still getting error when nulls are calculated I have
entered =Sum(NZ([Sales])) in the control source of the
text box that holds the calculation. It works when values
are available but error when Null Help
 
Even though the "Z" in Nz() represents zero, I would still recommend using
=Sum(Nz([Sales], 0))
If there are no records in your subreport then Nz() isn't going to help you.
You would need an expression like:
=IIf(subreport.Report.HasData, subreport.Report.txtYourTotal, 0)

--
Duane Hookom
MS Access MVP


Derek said:
THank you.

I am still getting error when nulls are calculated I have
entered =Sum(NZ([Sales])) in the control source of the
text box that holds the calculation. It works when values
are available but error when Null Help
-----Original Message-----
The NZ ("null to zero") built-in function can be used on each factor in the
calculation to convert it to zero before using it. It is necessary to
convert each factor because _any_ Null in an arithmetic calculation will
cause the calculation to have a Null result.

Larry Linson
Microsoft Access MVP




.
Click to expand...
Click to expand...
 
Thanks a million Duane

Will this also work in the subreport total. I hate it when
everything is not perfect. In other words can I stop the
error occuring in the subreport when no records or values
are available to total?
-----Original Message-----
Even though the "Z" in Nz() represents zero, I would still recommend using
=Sum(Nz([Sales], 0))
If there are no records in your subreport then Nz() isn't going to help you.
You would need an expression like:
=IIf(subreport.Report.HasData,
Click to expand...
subreport.Report.txtYourTotal, 0)
--
Duane Hookom
MS Access MVP


THank you.

I am still getting error when nulls are calculated I have
entered =Sum(NZ([Sales])) in the control source of the
text box that holds the calculation. It works when values
are available but error when Null Help
-----Original Message-----
The NZ ("null to zero") built-in function can be used
Click to expand...
on
each factor in the
calculation to convert it to zero before using it. It
Click to expand...
is
necessary to
convert each factor because _any_ Null in an arithmetic calculation will
cause the calculation to have a Null result.

Larry Linson
Microsoft Access MVP

Hi

I am using a subreport to total sales. When sales are null
it buggers up my grand total and VAT calculations on main
report. How do I say If Null show Zero

Thanks


.
Click to expand...
Click to expand...


.
Click to expand...
 
Don't ask if it works in a particular situation. Try it and report back.

--
Duane Hookom
MS Access MVP


Derek said:
Thanks a million Duane

Will this also work in the subreport total. I hate it when
everything is not perfect. In other words can I stop the
error occuring in the subreport when no records or values
are available to total?
-----Original Message-----
Even though the "Z" in Nz() represents zero, I would still recommend using
=Sum(Nz([Sales], 0))
If there are no records in your subreport then Nz() isn't going to help you.
You would need an expression like:
=IIf(subreport.Report.HasData,
Click to expand...
subreport.Report.txtYourTotal, 0)
--
Duane Hookom
MS Access MVP


THank you.

I am still getting error when nulls are calculated I have
entered =Sum(NZ([Sales])) in the control source of the
text box that holds the calculation. It works when values
are available but error when Null Help

-----Original Message-----
The NZ ("null to zero") built-in function can be used on
each factor in the
calculation to convert it to zero before using it. It is
necessary to
convert each factor because _any_ Null in an arithmetic
calculation will
cause the calculation to have a Null result.

Larry Linson
Microsoft Access MVP

message
Hi

I am using a subreport to total sales. When sales are
null
it buggers up my grand total and VAT calculations on
main
report. How do I say If Null show Zero

Thanks


.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Duane

I had tried it in every combination conceivable to me for
hours without success. I suppose if I had told you that
you may well have commented "don't struggle for hours
alone just ask the newsgroup" To assume that I had not
tried it was an error. But you have solved the problem so
thank you.
-----Original Message-----
Don't ask if it works in a particular situation. Try it and report back.

--
Duane Hookom
MS Access MVP


Thanks a million Duane

Will this also work in the subreport total. I hate it when
everything is not perfect. In other words can I stop the
error occuring in the subreport when no records or values
are available to total?
-----Original Message-----
Even though the "Z" in Nz() represents zero, I would still recommend using
=Sum(Nz([Sales], 0))
If there are no records in your subreport then Nz()
Click to expand...
isn't
going to help you.
You would need an expression like:
=IIf(subreport.Report.HasData,
Click to expand...
subreport.Report.txtYourTotal, 0)
--
Duane Hookom
MS Access MVP


THank you.

I am still getting error when nulls are calculated I have
entered =Sum(NZ([Sales])) in the control source of the
text box that holds the calculation. It works when values
are available but error when Null Help

-----Original Message-----
The NZ ("null to zero") built-in function can be
Click to expand...
used
on
each factor in the
calculation to convert it to zero before using it.
Click to expand...
It
is
necessary to
convert each factor because _any_ Null in an arithmetic
calculation will
cause the calculation to have a Null result.

Larry Linson
Microsoft Access MVP

message
Hi

I am using a subreport to total sales. When sales are
null
it buggers up my grand total and VAT calculations on
main
report. How do I say If Null show Zero

Thanks


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top