Date formula in access

[d9ly]

AS C/W task , need HELP!!! Graham Harrison - 1st post - 27 Feb 2004 13:02
I am creating a library db in the UK and I need sum help in implementing’ a way of charging fines and return due dates. A book is always due to be returned 1 calendar month after the date the book was borrowed. If the due date falls on a Saturday or a Sunday then the due date becomes the next Monday.
The fines are charged to members who return books after they r due bk. The fines charged are shown below.

If a book is 7 days late then a £0.30 charge is applied to them, If a book is 14 days late then £0.30 fine is added onto of the fine, if a book is 21 days late then £0.40 is added on top of the current fine, if a book is 28 days late then £0.50 is added on top of the fine. If thye book is 56 days late then a further £1.50 is added on top.

For e.g. person X was 32 days late returning her book and was fined £1.50 (£0.30 for 7 days, £0.30 for 14 days, £0.40 for 21 days + £0.50 for 28 days)

I have currently set up a books, members and loans table. And was wondering how to implement these problems into my db.

 

[lynchg]

The first thing you will want to do is set up a table that has a record for each occurrence of a person checking out a book, call it CheckedOutBook. You need to have at least the following fields:

BookID
MemberID
CheckOutDate

Then you can write code that enforces your rules for lateness. You'll need to use functions that determine the day of the week of the check out day in order to calculate the proper due date, etc. But once you get that CheckedOutBook table set up, I think you'll start to see how you can determine the rest of the info you need keep track of.

 

[synapsevampire]

What if there's a Holiday as a Monday, it's due on Tuesday, right?

One means to simplify this is to use a Period table as referenced in my FAQ:

faq767-4532

Then you can more readily determine the number of business days involved in any period.

Here's how you might do this by hardcoding it in Crystal:

faq767-4465

-k