Current and Past Balances

[Guest]

I am trying to make a query that generates a current balance on a record
using the date of an initial balance and the dates of payments made. This
way I can always make a query to get a balance from the past. So far I have
2 queries that almost do what I want:

Query Name: qryPaymentAmountSum
SELECT Sum(tblPaymentslst.PaymentAmount) AS [Total Paid],
tblPaymentslst.CaseNum
FROM tblPaymentslst
GROUP BY tblPaymentslst.CaseNum;

Query Name: qryCurrentBalance
SELECT tblBalances.CaseNum, tblBalances!InitBal-qryPaymentAmountSum![Total
Paid] AS CurBalqry
FROM qryPaymentAmountSum RIGHT JOIN tblBalances ON
qryPaymentAmountSum.CaseNum = tblBalances.CaseNum
GROUP BY tblBalances.CaseNum, tblBalances!InitBal-qryPaymentAmountSum![Total
Paid];

This almost does what I want except it if the case does not have any
payments the current balance is blank when it should actually be equal to the
initial balance.

If this is not enough to get the idea they please say so.

 

[jleckrone]

When you run your equation in your second query, it evaluates it as
InitBal - NULL and that's why you are getting a Null (blank) result.
Try this in place of your equation:
iif(isNull(TotalPaid), InitBal, InitBal - TotalPaid)

 

[Guest]

Perfect! Thank you so much! Didn't know how to use IF statements in queries.