Totals for 2 Currency Fields

  • Thread starter Thread starter Billsam8
  • Start date Start date
B

Billsam8

I am trying to get a total on 2 fields: 2009 amount & 2009 pledge amount -
all lower case.

I am using the following in the Report footer - I don't get an error but it
doesn't total:

=Sum([2009 amount]+[2009 pledge amount])

Here's the funny part - if I use the same fields together [2009
amount]+[2009 amount] or [2009 pledge amount]+[2009 pledge amount] it totals
correctly.

Can someone please help. Thanks in advance for your help.

Bill
 
If one of the fields is null then the sum will be null and then your
total will be null will test the record as if it were zero instead of
the sum of the two fields. You can try using the NZ function to force
zero values when the field is null.

=Sum(Nz([2009 amount],0)+Nz([2009 pledge amount],0))

or if there is always a sum for each field individually, then you could try

=Sum([2009 amount]) + Sum([2009 pledge amount])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
If one of the fields is null then the sum will be null and then your
total will be null will test the record as if it were zero instead of
the sum of the two fields. You can try using the NZ function to force
zero values when the field is null.

=Sum(Nz([2009 amount],0)+Nz([2009 pledge amount],0))

or if there is always a sum for each field individually, then you could try

=Sum([2009 amount]) + Sum([2009 pledge amount])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Hi John -

Thanks for the quick reply. I tried your second suggestion and it worked.

Thanks for the help. This was driving me nuts.
Bill

John Spencer said:
If one of the fields is null then the sum will be null and then your
total will be null will test the record as if it were zero instead of
the sum of the two fields. You can try using the NZ function to force
zero values when the field is null.

=Sum(Nz([2009 amount],0)+Nz([2009 pledge amount],0))

or if there is always a sum for each field individually, then you could try

=Sum([2009 amount]) + Sum([2009 pledge amount])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to get a total on 2 fields: 2009 amount & 2009 pledge amount -
all lower case.

I am using the following in the Report footer - I don't get an error but it
doesn't total:

=Sum([2009 amount]+[2009 pledge amount])

Here's the funny part - if I use the same fields together [2009
amount]+[2009 amount] or [2009 pledge amount]+[2009 pledge amount] it totals
correctly.

Can someone please help. Thanks in advance for your help.

Bill
Click to expand...
Click to expand...
 
Hi John -

Thanks for the quick reply. I tried your second suggestion and it worked.

Thanks for the help. This was driving me nuts.
Bill

John Spencer said:
If one of the fields is null then the sum will be null and then your
total will be null will test the record as if it were zero instead of
the sum of the two fields. You can try using the NZ function to force
zero values when the field is null.

=Sum(Nz([2009 amount],0)+Nz([2009 pledge amount],0))

or if there is always a sum for each field individually, then you could try

=Sum([2009 amount]) + Sum([2009 pledge amount])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am trying to get a total on 2 fields: 2009 amount & 2009 pledge amount -
all lower case.

I am using the following in the Report footer - I don't get an error but it
doesn't total:

=Sum([2009 amount]+[2009 pledge amount])

Here's the funny part - if I use the same fields together [2009
amount]+[2009 amount] or [2009 pledge amount]+[2009 pledge amount] it totals
correctly.

Can someone please help. Thanks in advance for your help.

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