Formula needed to calculate due date of books.

[MattyJacks]

I have a problem. Where in a library style database the book is loaned out on a certain date (loanDate), e.g. 15/3/11. I then need the query to automatically calculate the due date of the book in the form in the next field (returnDate).

E.g. book loaned out on 15/3/11 + 14 days = dueDate on 29/3/11. But hthis needs to be automatically calculated. Can anyone help me?

 

[JANR]

Use the DateAdd function.

In a Query:
Books Due: DateAdd("d", 14, [loaddate])

Controlsource of an unbound control on a form

=Dateadd("d", 14, [loandate])

JR

Edit: added information

 

[MattyJacks]

Thanks!! This calculates a due date but sometimes it calculates it -6 days and some other times it calculates it +14 days. Why is it doing this?? I am a bit confused..

 

[JANR]

That's not possible if your date field is defined as Date/Time, is it? or is it defined as text?

JR

 

[MattyJacks]

in the related tables those fields are all defined as Date/Time. if you don't believe me i could send the database to you. The form in the database is called STUDENT1

 

[JANR]

that's wierd, yeah you could post a stripped dow version where this behavior is documented.

I'm sure other posters would be interested in this "possible" bug, but for the record I have never come across this.

JR

 

[MattyJacks]

ok, the file is attached, the form is called STUDENT1. Thanks!

 

[JANR]

I'v looked and see other tread for solution, in your [fine] field on subform Loan Subform1

controlsource:

=DateDiff("d", DateAdd("d", 14, [loanDate]), [returnDate])*0.2

JR

 

[stopher]

You need to make the column width wider for the Due Date. At the moment it is cutting off the first digit of the date.

 

[JANR]

Thanks!! This calculates a due date but sometimes it calculates it -6 days and some other times it calculates it +14 days. Why is it doing this?? I am a bit confused..

No your dateadd function is correct, no bug there. I think you referd to the record for studenID 6 and 3 where loandate was 12/11/2010 and the Duedate date looked like 6/11/2010.

The solution was to increase the column width of the control to display 26/11/2010. easy mistake, so no bug.

JR

 

[MattyJacks]

Oh... i feel like a right idiot now. How did i not see that in the first place. Thanks for pointing that out. Any ideas with the due date method to fit with the term dates. This is because the due date works if there were no holidays, but there are so it doesn't.

My idea is:
=DATE() - search for this value in the 'term dates' table, look across to the relative autonumber linked to that date. And then count +14 records down the table to give the due date.
Is there any way to add this onto an Unbound field on a form, in the form of a module / formula / function??

 

[DCrake]

Is this a real exercise or is it part of an assignment?

 

[MattyJacks]

This is a job i have to set up for the librarian at my school however i have next to no expericnce with sql or access functions.