Additional inform to prev post - "Report Formula"

  • Thread starter Thread starter Formula in Report
  • Start date Start date
F

Formula in Report

I am using Access 2003
I just noticed that I add up items [Prod A] through [Prod C] into a 2nd
field called [Prod Total]. then I have a [freight] field and then a [return
credit] field. My [total cost] field actually adds [prod total]+
[freight]-[return credit]. As I looked at the all of the data in the
database, I noticed that my total cost only showed up when there was an
actual [return credit] involved. My return credit field has a $0.00 if
there are no credits involved. Does anyone have any idea as to what is
happening or how to correct this issue???

Aurora
 
Formula said:
I am using Access 2003
I just noticed that I add up items [Prod A] through [Prod C] into a 2nd
field called [Prod Total]. then I have a [freight] field and then a [return
credit] field. My [total cost] field actually adds [prod total]+
[freight]-[return credit]. As I looked at the all of the data in the
database, I noticed that my total cost only showed up when there was an
actual [return credit] involved. My return credit field has a $0.00 if
there are no credits involved. Does anyone have any idea as to what is
happening or how to correct this issue???
Click to expand...


Generally, when a value is not specified, it contains Null.
Null is a funny value because it represents something that
means Unknown. In an expression, e.g. [Prod A] + Unknown,
the total will be Unknown and display as nothing. I hope
you can make some sense of that, because it it a very
important concept that comes up a lot.

The Nz function is provided to help deal with this kind of
thing. Nz([Prod A], 0) will return 0 when [Prod A] is Null.

Putting all that together, your [Prod Total] text box
expression should be more like:
=Nz([Prod A],0) + Nz([Prod B],0) + Nz([Prod C],0)
and something similar for the total code text box.
 
Marshall
Thank you very much for your help. I redid the formula per your suggestion
and it worked beautifully. Again thank you very much for your help.

Aurora

Marshall Barton said:
Formula said:
I am using Access 2003
I just noticed that I add up items [Prod A] through [Prod C] into a 2nd
field called [Prod Total]. then I have a [freight] field and then a [return
credit] field. My [total cost] field actually adds [prod total]+
[freight]-[return credit]. As I looked at the all of the data in the
database, I noticed that my total cost only showed up when there was an
actual [return credit] involved. My return credit field has a $0.00 if
there are no credits involved. Does anyone have any idea as to what is
happening or how to correct this issue???
Click to expand...


Generally, when a value is not specified, it contains Null.
Null is a funny value because it represents something that
means Unknown. In an expression, e.g. [Prod A] + Unknown,
the total will be Unknown and display as nothing. I hope
you can make some sense of that, because it it a very
important concept that comes up a lot.

The Nz function is provided to help deal with this kind of
thing. Nz([Prod A], 0) will return 0 when [Prod A] is Null.

Putting all that together, your [Prod Total] text box
expression should be more like:
=Nz([Prod A],0) + Nz([Prod B],0) + Nz([Prod C],0)
and something similar for the total code text box.
Click to expand...
 
Back
Top