#Error when calculating a calculated field

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Using Access 2000, I am attempting to sum a field in a subform. I have
created a field named TOTAL in a subform which simply reads performs
=Sum([Amount]). This works great, but I really only want the total to
include items where the TRANSTYPE = "C".

Existing data ...
AMOUNT TRANSTYPE Total
$108.28 A $1,279.48
$100.00 A $1,279.48
$110.45 A $1,279.48
$105.33 A $1,279.48
$108.15 C $1,279.48
$111.58 C $1,279.48
$112.00 C $1,279.48
$113.13 C $1,279.48
$43.00 C $1,279.48
$42.96 C $1,279.48
$45.50 C $1,279.48
$43.53 C $1,279.48
$91.76 C $1,279.48
$46.61 C $1,279.48
$48.03 C $1,279.48
$49.17 C $1,279.48

Can this be done?
 
Tom said:
Using Access 2000, I am attempting to sum a field in a subform. I
have created a field named TOTAL in a subform which simply reads
performs =Sum([Amount]). This works great, but I really only want
the total to include items where the TRANSTYPE = "C".

Existing data ...
AMOUNT TRANSTYPE Total
$108.28 A $1,279.48
$100.00 A $1,279.48
$110.45 A $1,279.48
$105.33 A $1,279.48
$108.15 C $1,279.48
$111.58 C $1,279.48
$112.00 C $1,279.48
$113.13 C $1,279.48
$43.00 C $1,279.48
$42.96 C $1,279.48
$45.50 C $1,279.48
$43.53 C $1,279.48
$91.76 C $1,279.48
$46.61 C $1,279.48
$48.03 C $1,279.48
$49.17 C $1,279.48

Can this be done?
Click to expand...

How about

=Sum(IIf(TransType='C', Amount, CCur(0)))

Or else

=Sum(Amount*Abs(TransType='C'))
 
Thank you very much. I used the first of your suggestions and it worked
like a charm.

Thanks again!

Tom

Dirk Goldgar said:
Tom said:
Using Access 2000, I am attempting to sum a field in a subform. I
have created a field named TOTAL in a subform which simply reads
performs =Sum([Amount]). This works great, but I really only want
the total to include items where the TRANSTYPE = "C".

Existing data ...
AMOUNT TRANSTYPE Total
$108.28 A $1,279.48
$100.00 A $1,279.48
$110.45 A $1,279.48
$105.33 A $1,279.48
$108.15 C $1,279.48
$111.58 C $1,279.48
$112.00 C $1,279.48
$113.13 C $1,279.48
$43.00 C $1,279.48
$42.96 C $1,279.48
$45.50 C $1,279.48
$43.53 C $1,279.48
$91.76 C $1,279.48
$46.61 C $1,279.48
$48.03 C $1,279.48
$49.17 C $1,279.48

Can this be done?
Click to expand...

How about

=Sum(IIf(TransType='C', Amount, CCur(0)))

Or else

=Sum(Amount*Abs(TransType='C'))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top