Caculate a date a month away

[bestmatch]

Hey,

As part of a school project I have to create a library database. On
thing which I need to be able to do is automatically calculate a dat
which is a month away. There will be two fields one which is "date o
book borrowed" and the other is "date of book due back."

To calculate the "date of book due back" I have to use the field "dat
of book borrowed." The date of book due back should be a month afte
date of book borrowed. How do I do this and where? In form?

Also if the "date of book due back" falls on a saturday or sunday the
it must be changed to the next working day of Monday. How and where d
I calculate this?

Thanks if you can help me!!

 

[Steve Schapel]

Bestmatch,

It is obviously important to calculate the due date. But it is
incorrect to suggest that this should be in a field. You should only
have one field, LoanDate. The DueDate is derivative data which can
always be calculated when needed, for your purposes on form or report,
and thus should not be stored separately. The calculation is most
likely easiest in a calculated field in the query that your form or
report is based on. You can use the DateAdd() function.

 

[Douglas J. Steele]

Hate to argue with you on this one, Steve, but it may be appropriate to
store a due date in this case. How would you handle it if the library allows
renewals of loans? (Ours lets you renew a book up to 3 times, provided no
one has a request in for it)

 

[Steve Schapel]

It may be appropriate, Doug, but to do it like this really goes against
the grain for me. In an app I did for a library, there is a Loans table
with MemberID and ItemID, a LoanDetail table with LoanID, ActivityType,
and ActivityDate, and the ActivityType is linked to an Activities table
with options such as "Book loan", "Book renewal", "Video loan",
"Return", etc, each of which (except Return and Lost) has a NumberOfDays
associated with it. So Video loan is 7 days and Video renewal is 3
days, etc. It works sweetly. Due date is easily found in a query, and
a report of overdue items is based on this.

Besides which, Doug, Bestmatch is doing a school project here, so I
thought it was best to emphasise correct principles!

 

[Mike Sherrill]

The DueDate is derivative data which can
always be calculated when needed, for your purposes on form or report,
and thus should not be stored separately.

The fact that data can be derived doesn't determine whether it should
be stored.

Whether to store derived data is almost always an application
performance issue. And, in Access applications, it's almost always
done by leaving out some integrity constraints, which is pretty risky.

 

[Mike Sherrill]

As part of a school project I have to create a library database. One
thing which I need to be able to do is automatically calculate a date
which is a month away.

It's pretty unusual for the due date to be a month from the checkout
date. The checkout period is usually in days. Where I live, the
public library checks out books for 21 days, DVDs for 3 days,
paintings for 30 days. The university library checks out books to
undergraduate students for 28 days, I think, but graduate students get
an indefinite period--the book is due a week after somebody else asks
for it.

Anyway, look at DateAdd(), DateValue(), Weekday(), and Iif()
functions. Think about what happens in leap years.

Also if the "date of book due back" falls on a saturday or sunday then
it must be changed to the next working day of Monday. How and where do
I calculate this?

Don't count on the next working day to be a Monday. And isn't it odd
that you can check out books on Sunday, but you can't them on Sunday?

Your best bet is probably to use a calendar table. But that will also
probably get you in trouble in school. Failing that, calculate the
due date in a query. Use an expression made up of Iif(), DateAdd(),
and Weekday().

 

[Fred Boer]

Dear Mr. Schapel:

I am an amateur Access user who has developed a simple library program for
my school library. It uses a rudimentary due date process of simply
calculating 21 days. I am very interested in your description of a due date
system, and I want to try to implement something like this in my own
application. Please forgive me if I'm acting inappropriately (I ain't too
proud to beg... and I have a lot of nerve, I know!<g>), but I am wondering
if you might be willing to share with me more details about how this works?
Have you listed all of the tables necessary? What does the due date query
look like? Is there any way you could make a demo of this available? Of
course, I completely understand if this isn't possible; it is your
proprietary code, after all!

In any case, the description you have provided has given me enough to make a
start; and I appreciate that you have posted these details! BTW, there are
likely to be many posts from me on this topic as I apply my formidably
limited Access skills to this problem!

Cheers!
Fred Boer