Expression error (1 Viewer)

[faizulhu]

I am trying to create a expression to calculate the net fine of a book by the expression below by using a query:

Net Fine: IIf(DateDiff("d",[Issue Date],Date())>7 And [Returned]=True,(DateDiff("d",[Issue Date],Date())-7)*15,"0")

The returned field is a checkbox. The expression works but the record keeps updating and as a result the net fine increases everyday. So is there any way to keep the fine from increasing. I know then problem is occuring due to the usage of Date(). And I dont want use VB for this task.
And is there any way to set its data type to currency.

 

[gemma-the-husky]

currency type: right click the column and pick a currency format

how does the record [incidentally, what do you mean by record?] keep updating? Is [net fine] a field in the table, or just an extra column in the query?

 

[faizulhu]

A record is a row.

Updating here means that whenever the query is run, the column recalculates itselt with the current date(which changes everyday) resulting in the increase in net fine. [Net Fine] is a field

 

[gemma-the-husky]

when you levy a fine, you probably need to store the amount of the fine levied and charged, and only calculate new fines for those late returns not already charged - you will know the logic you want to apply.