Calculated field subtotal & total issue

[BrotherBook]

Hi-

I have a calculated field in my report called "New Business Revenue". I used the expression builder to calculate this value for each item in my report. I subtotal my report using a field called "Stage". In the "Stage" footer field I am trying to sum my calculated "New Business Revenue" field and then also have an overall total in my report footer.

Here is the code I use to calculate the "New Business Revenue" for each entry.

=IIf([Product]="Term Loan" And [Index]="Prime",[Commitment Amount]*([Spread]+0.027)+[Fee],
IIf([Product]="Term Loan",[Commitment Amount]*[Spread]+[Fee],
IIf([Product]="Line of Credit" And [Index]="Prime",([Commitment Amount]*0.5)*([Spread]+0.027)+[Fee],
IIf([Product]="Line of Credit",([Commitment Amount]*0.5)*[Spread]+[Fee],0))))

I split the iff statements into separate lines for ease of reading. The statement works fine for line items, but I can't figure out how to get it to subtotal.

Any help would be appreciated as this is my first attempt at report writing.

 

[pbaldy]

Have you tried enclosing the whole thing in a Sum() function?

=Sum(IIf([Product]="Term Loan" And [Index]="Prime",[Commitment Amount]*([Spread]+0.027)+[Fee],
IIf([Product]="Term Loan",[Commitment Amount]*[Spread]+[Fee],
IIf([Product]="Line of Credit" And [Index]="Prime",([Commitment Amount]*0.5)*([Spread]+0.027)+[Fee],
IIf([Product]="Line of Credit",([Commitment Amount]*0.5)*[Spread]+[Fee],0)))))

I should add it's typically not a good idea to have business rules/amounts hidden in code like that. I'd have them in a table or something more dynamic.

 

[BrotherBook]

In which control source? I tried it in my "Stage Header" textbox control source and it results in a #error.

I have also tried using the same control source here and change "Running Sum" to "Over Group", but that is not working either. Stage 1 has two entries $79,000 and $0. Putting the same code in the "Stage Header" control source and setting "Running Sum" to "Over Group" results in $0.

 

[pbaldy]

I was thinking in a textbox in a group or report footer. Can you post the db here?

 

[BrotherBook]

I calculate these fields in my forms, but Access was not letting me save down these values into the table. When i posed that question on the Forms board, people told me never to save calculated fields down to my table.

I would prefer to just have these calculated fields saved down into my table. If you're saying that's possible I can post my VBA to calculate the fields and maybe we can work on why Access won't let me write down these calculated fields.

 

[pbaldy]

It's more a "shouldn't" than a "can't":

http://allenbrowne.com/casu-14.html

 

[BrotherBook]

Gotcha. The issue I have been running into is that my VBA calculates the amount correctly, but always writes down $0. I put in message boxes to test the calculation of values, but it always seems to lose the value before writing down to field.

Private Sub RunCalcs()

Dim CouponCalc As Double
Dim PrimeSpread As Double

PrimeSpread = 0.027

If Me.ProductType = "Loan" And IsNull(Me.Commitment_Amount) = False And IsNull(Me.Spread) = False And IsNull(Me.Cost_of_Funds) = False Then
CouponCalc = Me.Cost_of_Funds.Value + Me.Spread.Value

Me.Coupon.Value = CouponCalc

If Me.Product = "Term Loan" And Me.Index = "Prime" Then
Me.New_Business_Revenue.Value = Me.Commitment_Amount.Value * (Me.Spread.Value + PrimeSpread) + Me.Fee.Value
Else
If Me.Product = "Term Loan" Then
Me.New_Business_Revenue.Value = Me.Commitment_Amount.Value * Me.Spread.Value + Me.Fee.Value
Else
Me.New_Business_Revenue.Value = 0
End If
End If

If Me.Product = "Line of Credit" And Me.Index = "Prime" Then
Me.New_Business_Revenue.Value = (Me.Commitment_Amount.Value * 0.5) * (Me.Spread.Value + PrimeSpread) + Me.Fee.Value
Else
If Me.Product = "Line of Credit" Then
Me.New_Business_Revenue.Value = (Me.Commitment_Amount.Value * 0.5) * Me.Spread.Value + Me.Fee.Value
Else
If Me.New_Business_Revenue > 0 Then
Else
Me.New_Business_Revenue.Value = 0
End If
End If
End If

Else
End If

End Sub

Both Me.Coupon.Value & Me.New_Business_Revenue.Value yield zero even with proper inputs.

The "IsNull" piece of the if statement cancels the calculation if the necessary fields have not been populated. This is because i call this command in several "AfterUpdate" events in case the inputs change.

Do you see any reason why Access wouldn't write down the calculated fields?

 

[BrotherBook]

It looks like Me.Coupon.Value is the only field causing the issue. New Business Revenue is now calculating correctly and saving down.

 

[BrotherBook]

I appreciate you patience. I just double checked and somehow my coupon field was set to Long Integer. That would be the problem.

You've helped me solve all of my issues! Thanks a bunch.

 

[pbaldy]

Happy to help!

 

[BrotherBook]

This is what happens when people ask an Access newbie to create a database from scratch. This website has been a godsend.