Calculation not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Yesterday I posted this question :

"I have a form called "Investments" and then a subform set up to record
detailed investment transactions for each investment. I would like to add
the "Investment cost" field in the footer of the subform based on only those
detailed transactions with no check in the check box for "replaced/sold"
(which means they are still active investments).
Normally I would just set up an unbound text box and use
=sum([InvestmentCost]) to calculate that field, but how do I bring into this
formula only those with no check?"

It was suggested I use =Sum(IIF[replaced/sold]=0,[InvestmentCost],0)) which
didn't work. It just shows $0.00. I tried
=Sum(IIF[replaced/sold=False,[InvestmentCost],0)) and that also only produced
$0.00.

Does anyone else have any ideas?
 
I assume [replace/sold] is bound to a Yes/No field, correct? If so, you can
use that in your calculation. Try this:

=sum([InvenstmentCost]*Abs([replace/sold]))

Since a Yes/No field stores a -1 for Yes and 0 for No, if it is yes, you'll
be multiplying InvestmentCost by the absolute value of -1 (i.e. 1) leaving
the same number. If it's No, you'll multiply your cost by zero, which will
leave zero.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Once I deleted the text box and created a new one, the other formula worked.
Actually this worked also...so thank you very much
--
Thank, Lori


Roger Carlson said:
I assume [replace/sold] is bound to a Yes/No field, correct? If so, you can
use that in your calculation. Try this:

=sum([InvenstmentCost]*Abs([replace/sold]))

Since a Yes/No field stores a -1 for Yes and 0 for No, if it is yes, you'll
be multiplying InvestmentCost by the absolute value of -1 (i.e. 1) leaving
the same number. If it's No, you'll multiply your cost by zero, which will
leave zero.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Lori said:
Yesterday I posted this question :

"I have a form called "Investments" and then a subform set up to record
detailed investment transactions for each investment. I would like to add
the "Investment cost" field in the footer of the subform based on only
those
detailed transactions with no check in the check box for "replaced/sold"
(which means they are still active investments).
Normally I would just set up an unbound text box and use
=sum([InvestmentCost]) to calculate that field, but how do I bring into
this
formula only those with no check?"

It was suggested I use =Sum(IIF[replaced/sold]=0,[InvestmentCost],0))
which
didn't work. It just shows $0.00. I tried
=Sum(IIF[replaced/sold=False,[InvestmentCost],0)) and that also only
produced
$0.00.

Does anyone else have any ideas?
Click to expand...
Click to expand...
 
Back
Top