Expressions in Access Reports

[Reese]

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

 

[Marshall Barton]

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

=Sum(IIf([Sale Type] = "new", Income, 0))

 

[Reese]

Thank you so much. Worked at this all day and you answered it! I'm sure
I'll be back, thanks again....

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

=Sum(IIf([Sale Type] = "new", Income, 0))

 

[Reese]

Thank you very much. I spent all day on it. I'm sure I'll be back for more
answers.

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

=Sum(IIf([Sale Type] = "new", Income, 0))

 

[Reese]

Another problem I'm having is that I'm writing expressions that return the
correct mathmatical results on the report only they're all showing either as
negaitve values or in parethases. But, the answer is correct.

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

=Sum(IIf([Sale Type] = "new", Income, 0))

 

[Marshall Barton]

Another problem I'm having is that I'm writing expressions that return the
correct mathmatical results on the report only they're all showing either as
negaitve values or in parethases. But, the answer is correct.

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

=Sum(IIf([Sale Type] = "new", Income, 0))

Either the Income values are negative or you used a
variation of my suggested expression.

I might get a clue if you posted some sample data from the
**table** along with a Copy/Paste of the expression you
used.

 

[fredg]

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

Add an Unbound control to your report (but not in the Page Header or
Footer).
Set it's control source to:

=Sum(IIf([Sale Type]= "new",[Income],0))

 

[Reese]

Here is one of the formulas I used that is returning the correct number of
deals, but showing on the report as a negative number i.e. -4

=Sum([New / Used]='new')

Another problem I'm having is that I'm writing expressions that return the
correct mathmatical results on the report only they're all showing either as
negaitve values or in parethases. But, the answer is correct.

Reese wrote:

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese


=Sum(IIf([Sale Type] = "new", Income, 0))

Either the Income values are negative or you used a
variation of my suggested expression.

I might get a clue if you posted some sample data from the
**table** along with a Copy/Paste of the expression you
used.

 

[fredg]

Here is one of the formulas I used that is returning the correct number of
deals, but showing on the report as a negative number i.e. -4

=Sum([New / Used]='new')

Another problem I'm having is that I'm writing expressions that return the
correct mathmatical results on the report only they're all showing either as
negaitve values or in parethases. But, the answer is correct.

:

Reese wrote:

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

=Sum(IIf([Sale Type] = "new", Income, 0))

Either the Income values are negative or you used a
variation of my suggested expression.

I might get a clue if you posted some sample data from the
**table** along with a Copy/Paste of the expression you
used.

So you wish to count the number of times [New / Used]='new'?

Access is correctly displaying the result of your expression.
Your expression, expressed verbally is
=Sum(Something is true)

Since True is -1, you are adding -1 for each time the expression
evaluates as true.

To return a positive value, you can use:

=ABS(Sum([New / Used]="new"))

ABS() returns a positive result.

or, you could use:

=Sum(IIf([New / Used]="new",1,0))
which simply adds 1 each time the criteria expression is true, or 0 if
the expression is false.

 

[Marshall Barton]

So, you did use a variation, in which case do what Fred
said.

It's always a good idea to post what you did along with your
question to avoid going back and forth or having people
guess at that problem.
--
Marsh
MVP [MS Access]

Here is one of the formulas I used that is returning the correct number of
deals, but showing on the report as a negative number i.e. -4

=Sum([New / Used]='new')

Another problem I'm having is that I'm writing expressions that return the
correct mathmatical results on the report only they're all showing either as
negaitve values or in parethases. But, the answer is correct.

:

Reese wrote:

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese


=Sum(IIf([Sale Type] = "new", Income, 0))

Either the Income values are negative or you used a
variation of my suggested expression.

I might get a clue if you posted some sample data from the
**table** along with a Copy/Paste of the expression you
used.

 

[Reese]

Your answers have been very helpful. Thank you.
My last inquiry is about expressions that return a value of "#Num!"
The calculations for these particular fields will result in zero because
there was no sale recoreded in the field that they are derived from, but I'd
like to value to show "0.00" instead of "#Num!" Can you help?

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

Add an Unbound control to your report (but not in the Page Header or
Footer).
Set it's control source to:

=Sum(IIf([Sale Type]= "new",[Income],0))

 

[fredg]

Your answers have been very helpful. Thank you.
My last inquiry is about expressions that return a value of "#Num!"
The calculations for these particular fields will result in zero because
there was no sale recoreded in the field that they are derived from, but I'd
like to value to show "0.00" instead of "#Num!" Can you help?

I'm trying to build an expression in an Access Report that adds values in one
field for only records that meet certain criteria from another field. Ex.
Add total income in the [Income] field for only those records whose [Sale
Type] field = "new". Access 2007. Thanks, Reese

Add an Unbound control to your report (but not in the Page Header or
Footer).
Set it's control source to:

=Sum(IIf([Sale Type]= "new",[Income],0))

I would suggest you re-post, starting a new thread, as this question
is not the same as the original one. Other readers, who might have a
similar question, might not see it here, and we would like to help as
many people as we can.

Also, I would recommend you include the exact full expression you are
using that results in the #num error, so that we don't have to guess.