New to access and need help with query

[zero3ree]

I am trying to set up a query to calculate a fee after calculating how many days overdue. I have DateDue and DateReturned and DollarValue . I need to develop a criteria that creates a fine that $1 + days overdue*1% of the Value of the item. On a side not I also would like query to only show overdue dates. Here is my query




Any help is appreciated

[maximus]

Let me get it straight you need to calculate the difference between Date due and date returned if date returned is later than date due.

Then add $1 to the product of 1% of the value of the product and the days over due. If this is what you want then here is the solution:

in your query add a calculative field type the following code:
Fee: IIf(DateDiff("d",[DateDue],[DateReturned])<=0,0,1+(DateDiff("d",[DateDue],[DateReturned])*[Value]*1/100))

I have assumed that if that if it is returned on the return date or before it no fee is to be calculated and thus it remains 0.

To show the over due days I hope you mean that and not dates make a calculative field with the following code:


if you dont want to show negative quantity which may occur if returned before due date use the code below.

IIf(DateDiff("d",[DateDue],[DateReturned])<=0,0,DateDiff("d",[DateDue],[DateReturned]))

the secode code will show 0 if return date is equal to or earlier that due date.


if this solves your problem please mark the thread to be solved


I am attaching a image please refer to it also.