Circular Reference Problem

[dsomers]

Hey all! I'm getting a circular reference problem in a query I'm using and unsure how to resolve it. Here's what I've got:

- I have a field called Balance which stores the total balance of a tenant. Mathematically Balance is equal to:

Balance = (Prev Balance + Monthly Rent + Late Fee) - Rent Paid

Late Fee = [MonthlyRent]*0.05

I'm using an IIf() in the expression for balance as follows:

Balance: IIf([Balance]>0,[Balance]=([Prev Balance]+[MonthlyRent]+[Late Fee])-[Rent Paid],[Balance]=([Prev Balance]+[MonthlyRent])-[Rent Paid])

When I try to open up the query to see if the figures are calculated correctly I get a Circular Reference caused by alias 'Balance' in query definition SELECT list. I'm no Access expert by no means, and this is the first time I've come across this error. Can someone tell me how to resolve this problem?

Thanks!
David Somers

 

[Rich]

You shouldn't be storing the balance, it can be calculated by simply Summing the amount due less the amount paid.

 

[dsomers]

I'm using that IIf() to determine whether the balace is 0 though. How would I rewrite that without storing balance?

Thanks!
David Somers

 

[dsomers]

Well, I tried it and it worked. hehe.. thanks!

Now with the DateDiff() function, I need to be able to determine if the date is between m/1/yyyy and m/5/yyyy, not to add the late fee. Any date after the 5th, add the late fee.

How would I use the DateDiff() function to accomplish this?

Thanks!
David Somers