Calculated sum doesn't show negative

  • Thread starter Thread starter William Martinez
  • Start date Start date
W

William Martinez

I'm trying to add a calculated sum to a report which is
the difference between two database fields:

=sum([fieldB]-[fieldA])

The fields are currency.

The calculation works OK except when fieldA is greater
than fieldB. Then it doesn't return the correct negative
number. It returns a positive number. Any help would be
appreciated. Thanks.

Will
 
The second case. I actually changed the format to a
regular number just to see what would happen, and the
result was the same.

Will
 
Hi Cheryl, thanks for the reply. Actually I did try that
(and a lot of other desperate things too), but I finally
figured out what was going on. One of the fields was null
and I guess Access wasn't smart enough to figure out that
nulls should be the same as 0's. When I repaced the
nulls, everything worked fine.

Will
-----Original Message-----
Have you tried:

=[FieldB] - [FieldA]


hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The second case. I actually changed the format to a
regular number just to see what would happen, and the
result was the same.

Will
Click to expand...


.
Click to expand...
 
Anamarie, thanks for you reply. I finally figured out the
problem was being caused by null values in one of the
numeric fields.

Will
 
Take a look at the NZ() function ... just for future reference.

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

William Martinez said:
Hi Cheryl, thanks for the reply. Actually I did try that
(and a lot of other desperate things too), but I finally
figured out what was going on. One of the fields was null
and I guess Access wasn't smart enough to figure out that
nulls should be the same as 0's. When I repaced the
nulls, everything worked fine.

Will
-----Original Message-----
Have you tried:

=[FieldB] - [FieldA]


hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The second case. I actually changed the format to a
regular number just to see what would happen, and the
result was the same.

Will
-----Original Message-----
using the standard format
a b c
$7.00 $9.00 -2.00
$1.00 $3.00 -2.00
$3.00 $1.00 2.00
$6.00 $4.00 2.00

OR using the format as currency
a b c
$7.00 $9.00 ($2.00)
$1.00 $3.00 ($2.00)
$3.00 $1.00 $2.00
$6.00 $4.00 $2.00


.
Click to expand...


.
Click to expand...
Click to expand...
 
Yea since null is not a number it had a type mismatch.

--
Joseph E. Meehan

26 + 6 = 1 It's Irish Math


William Martinez said:
Hi Cheryl, thanks for the reply. Actually I did try that
(and a lot of other desperate things too), but I finally
figured out what was going on. One of the fields was null
and I guess Access wasn't smart enough to figure out that
nulls should be the same as 0's. When I repaced the
nulls, everything worked fine.

Will
-----Original Message-----
Have you tried:

=[FieldB] - [FieldA]


hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

The second case. I actually changed the format to a
regular number just to see what would happen, and the
result was the same.

Will
-----Original Message-----
using the standard format
a b c
$7.00 $9.00 -2.00
$1.00 $3.00 -2.00
$3.00 $1.00 2.00
$6.00 $4.00 2.00

OR using the format as currency
a b c
$7.00 $9.00 ($2.00)
$1.00 $3.00 ($2.00)
$3.00 $1.00 $2.00
$6.00 $4.00 $2.00


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top