Adding additional fees to balance and subtracting payments

[dedren]

On a form I am trying to display the following with data from different subforms:

casebalance + sum of additional fees - sum of all payments=current balance

I started with the following expression:

=Sum((sbfBalance.Form!InitBal+sbfFees.Form!SumFees)-sbfPayments.Form!SumPayment)

I found out that if any of the fields involved are null it returns a #Error. I did a good bit of research on tek-tips and via Google and thought I had the solution in the expression below but it still gives me the #Error if the [SumFees] portion is null:

=IIf(IsError(sbfPayments.Form!SumPayment) Or IsNull(sbfPayments.Form!SumPayment),sbfBalance.Form!InitBal,sbfBalance.Form!InitBal+sbfFees.Form!SumFees-sbfPayments.Form!SumPayment)

I don't understand what I am doing wrong or how to fix it, so I would greatly appreciate some assistance.

Thank you in advance

 

[dhookom]

I probably wouldn't even reference the subform controls. All of the values should be in tables where you can query them directly.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

If I understand what you are saying, I could do something like this on the form?:

=(tblBalances.InitBal+Sum(tblFees.FeeAmount))-tblPaymentslst.PaymentAmount

I am terrible with syntax but I tried the above and got the error #Name.

Also, would this update as soon as the user entered a payment/fee and moved to the next control?

 

[dedren]

I couldn't edit my last thread to ask if it mattered whether I put this control in the main form or in the form footer. I get the same #Name error for both currently.

A little more history on this:
Before I had to add "Fees" to the balance I had a field in the header section of the payments subform that summed all of the payments and then subtracted that from the initial balance. It worked great. It updated instantly and made everyone merry. Now they need to add court fees and such so I had to make a change.

 

[dhookom]

If you want to reference values in tables, you can use either code to create a recordset or you can use domain aggregate functions such as DSum() or DCount().

I would need to know your table and field names as well as how they are related. You might need to use something like:

=InitBal+DSum("FeeAmount","tblFees", "CaseNumber=" & [CaseNumber])

This assumes you have a parent/child relationship on a numeric field named CaseNumber. I expect my guess is off but I hope you get the idea.

Duane
Hook'D on Access
MS Access MVP

 

[dedren]

Your guess is pretty darn close. All involved tables are linked with the alphanumeric field called [CaseNum] (ex: 09-1234).

The main table is: [tblCasesMain]
The balance table is: [tblBalances] with [InitBal] as the pertinent value.
The fees table is: [tblFees] with [FeeAmount] as the pertinent value.
The payment table is: [tblPaymentslst] with [PaymentAmount] as the pertinent value.

Is your DSum statement complete? In your example how does DSum take the [CaseNum] from the active main form? and how does it subtract the total of [PaymentAmount]?

 

[dhookom]

CaseNum is pulled from the main form where I had

   =" & [CaseNumber])

I didn't include the payment calculation because I expected you to be able to add it in once you understood the field DSum().

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Since you CaseNum isn't numeric, try:

=InitBal+DSum("FeeAmount","tblFees", "CaseNum=""" & [CaseNum] & """")

If this works to add the fees, then you should add the payments expression.

Duane
Hook'D on Access
MS Access MVP