=Sum(IIf([TransactionType] Like

[access2010]

Hello, I am trying to add up two columns of figures and am having a problem, which probable requires a simple solution.

Column name = TransactionType
Group name = Bought_$ = this field will always have a quantity
Group name = DRIP = this field may not be used

I am trying to add up the total quantities of all the Bought_$ and Drip quantities and have used the two codes below without success.

=Sum(IIf([TransactionType] Like "*Bought_$*" Or Like "*DRIP*",[TransactionQuantity],0))

=Sum(IIf([TransactionType] Like "*Bought_$*" + Like "*DRIP*",[TransactionQuantity],0))

Your suggestions, will be appreciated.
Paul

 

[theDBguy]

Hi Paul. Try it this way:

=Sum(IIf([TransactionType] Like "*Bought_$*" Or [TransactionType] Like "*DRIP*",[TransactionQuantity],0))

 

[access2010]

theDBguy
Thank you, your formula works
Paul

 

[theDBguy]

theDBguy
Thank you, your formula works
Paul

Hi Paul. It's actually your formula. I just tweaked it for you.

 

[access2010]

theDBguy
The formula that you tweaked for me works in ALL the cases except in this instance.
=Sum(IIf([TransactionType] Like "*Bought_$*" Or [TransactionType] Like "*DRIP*",[TransactionTotal],0))

===
The field = TransactionTotal = uses the formula below
=Nz([TransactionQuantity])*Nz([TransactionPrice])+NZ([TransactionComm])

===
Thank you for your assistance.
Paul

 

[essaytee]

Hi Paul,


Just for your info, and if I'm on the right track, based on this post from another thread, excerpt here:

As an aside, I noticed that the TransactionQuantity per line is either positive or negative. The negative entries relate to 'Sold_$'. Without directly referring to the Transaction Type (Bought, Sold, Drip) it would be possible, in the query, to only restrict entries where the TransactionQuantity > 0 (must be either Bought or Drip (what ever Drip is)).

​

Therefore, you don't need to specifically check for Bought or Drip, just check against a positive value of TransactionQuantity. Just some food for thought.

 

[access2010]

Hello, essaytee
===
Thank you for your note.
Paul (and I think you) has suggested that we tweak the database as the calculation for average cost was not correct.
Bought is always a positive value
Sold is not required in any calculation on this form
Drip is always a positive.
===
The field = BoughtDripQuantity = calculation is correct
The field = BoughtDripDollars = calculation is not correct
===
The last field required = BoughtDripAverageCost
Will divide = BoughtDripDollars = by = BoughtDripQuantity
=Sum(Nz([BoughtDripDollars])/Sum([BoughtDripQuantity])
===
After these calculations are complete, I think we should have the proper calculations required.
===
(Drip = Instead or receiving a dividend, we receive the same value in stock, Dividend Reinvestment Plan)
===
Could I please receive assistance on how to correct, the field
= BoughtDripDollars = as the calculation is not correct
===
I/we appreciate your assistance in bringing this project to a conclusion.
Nicole

 

[essaytee]

The reason why one of your sum calculations worked and the other didn't is that the one working (TransactionQuantity) referred directly to a field of the recordset. The other sum calculation (TransactionTotal) didn't work because you were referring to a non-field control source. That is, the TransactionTotal is not a field from the recordset, it is actually, itself, calculated per line. In order to sum the TransactionTotal textbox, repeat the calculation within the sum calculation.

Based on the above, I also condensed your formula whereby it checks against a positive TransactionQuantity as opposed to checking if Bought or if Drip.

Control Source calculation for Transaction Quantity:

=Sum( IIf( [TransactionQuantity] > 0, [TransactionQuantity], 0 ) )

Control Source calculation for Transaction Total:

=Sum( IIf( [TransactionQuantity] > 0, ( [TransactionQuantity]* [TransactionPrice] ) + [TransactionComm] ) )

Note: I added spaces for readability, but you should still be able to copy paste direct into your control sources.

I'll leave the calculation for the Average Cost for you, base it upon the above.

Edit: I learnt something new myself. The above was a head-scratcher at first; I didn't pick up on the solution on first viewing.

 

[access2010]

essaytee
99.99% complete
Thank you for your assistance, and nearly everything that I wanted to use this Ms Access program for has been accomplished.
===
A few piddly things have to be done but with the help of all these Forum people lots of ideas have been put into action.
===
Thanks to everyone
Paul and assistants

 

[access2010]

Steve
TransactionQuantity > 0
===
The above works and thank you
Nicole

 

[essaytee]

Steve
TransactionQuantity > 0
===
The above works and thank you
Nicole

No worries, glad I could help.