Formula needed to calculate fines

[MattyJacks]

In a library book database i need a formula to calculate fines for every day overdue the book is of the return date.

E.g. - returnDate = 15/03/2011 and dateReturned = 20/03/2011.
Difference = 5 days. So fine = 5 x £0.20 = £1.00.

I need a formula to be inserted into an unbound field in a form to calculate this. Anyone have any ideas??

 

[JANR]

You shuld look in Access help for Date Functions, in this case the DateDiff function

=Datediff("d", [returndate],[datereturned])*0.2

JR

 

[MattyJacks]

I am a bit of a beginner with Access i must admit. I typed in the formula above and changed the field names appropriately and it just said NAME?# could you have a look at that also while you have the database. I have attached it onto the other thread.

 

[JANR]

Lets take a step back. You have a database with books, When a book is lend out then this is recorded in [loanOut] you then record when the book is physically delivered back [DeliveredDate]

Those 2 date fields is all that you need to calculate overdue books using Dateadd function, then you need to calculate latefees that uses Datediff.

So to calculate latefees in a form you have a Unbound control on that form and in it's controlsource you put someting like this:

=Datediff("d", DateAdd("d", 14, [loanOut]), [DeliveredDate])*0.2

I'm I on the right track?

JR

 

[MattyJacks]

Thats great, thanks!! Thats sorted the Fines part but i am still stuck with the form giving me minus dates for the DueDate and i cannot delete any fields, due to the fact that i have been instructed by my boss to have those specific fields. Due date = 14 days after loanDate, excluding weekends when the book should be back, and returnDate is when the book was physically returned.

This is what my brief was on return dates:

A book can be on loan for fourteen days. The due date is the Loan date plus fourteen days as shown
in Table 6.1. If the Due date falls on a day when the school is closed (e.g. during a holiday period) then
the Due date becomes the date that the school reopens. A fine of £0.20 per school day is charged to
borrowers who return books after the Due date. If a book is returned more than fourteen school days
after the Due date there is an additional £1.00 charge.
Examples:​

•​

​

​

​

​

If a book is due back on Friday and is returned on the following Monday, the fine is £0.20.​

•​

​

​

​

​

If a book is due back on Wednesday the week before half term and is returned on the Tuesday​

after half term, the fine is £0.80.

The dates of the term are in the Term_Dates table.

 

[JANR]

See other thread.

JR