Problems with Calculations

  • Thread starter NickOconell via AccessMonster.com
  • Start date
N

NickOconell via AccessMonster.com

Formula as follows

SumI= IIf([TypeofHolding].[Value]="I",IIf([%ofholding]>5,([%ofholding]),0),0)
SumP= IIf([TypeofHolding].[Value]="P",IIf([%ofholding]>5,([%ofholding]),0),0)
This formula is written in control Source of a form

TypeofHolding % holding SumI SumP TotI TotP
P 4
Error Error
I 6 6 6
Error Error
I 8 8 8
Error Error
P 12 12
Error Error

For TotI I tried different formulas, I am getting error or Blank cell.
TotI= Sum([SumI]
TotI=Sum(Nz(SumI))
Total= CDbl(Sum([SumI]))
TotI=Dsum("SumI", "TableName", "CompanyID=" & CompnayID")

I have tried in bound and Undound Textbox in form footer asweell, but it is
not working

Finally I have to calculate Total(I&P)
Total(I&P)=sum([TotI])+Sum([TotP])

I am aware Sum() function cannot use on controls. Sum() only works for table
fields.
I have tried VBA code as well, to find SumI

Private Sub SumI_BeforeUpdate(Cancel As Integer)
Dim C As Double
Dim P As Double
Dim TotI As Double
C = Me![SumI]
P = Me![SumP]
TotI = DSum("C", "DetailsofSholder", "[CaseID]=" & [CaseID])
End Sub
Again VBA code isn't working

Could someone please help me out,
Nick
 
Top