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')
Marshall Barton said:
Reese said:
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.