Date Automatic Increment

casscar
casscar used Ask the Experts™
on
Hi,

I'm working on a database for a book library. I would like to input the Issue Date for a borrowed book and then i would like to have the Due Date (book return date) to be automatically generated as two weeks from the issue date. Can this be done? If yes, how?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you can add Add a calculated field to a table
refer
http://office.microsoft.com/en-us/access-help/add-a-calculated-field-to-a-table-HA101820564.aspx
http://allenbrowne.com/casu-14.html

try for date

DateAdd ("ww", 2, ISSUE_DATE)

or
DateAdd ("d", 14, ISSUE_DATE)

Commented:
DateAdd ("ww", 2, ISSUE_DATE)-1

becuase
for example if you run
issue date equal 01/08/2011

DateAdd ("ww", 2, ISSUE_DATE)
the results will come

15/08/2011

Its wrong becuase of that you need to deduct one day to get correct day

Author

Commented:
Hi,

Thanks for your help and prompt reply. The thing is that i'm still getting the attached error when trying to save. Access Error for Automatic Issue_Date
change format to Date
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Don't add a field to the table as you can always calculate the due date:

In a query:
DateDue: DateAdd("d",14",[DateIssued])

As controlsource for a textbox:
=DateAdd("d",14",[DateIssued])

/gustav
Top Expert 2010

Commented:
pratima_mcs,

Kind of ironic that you posted Allen Browne's page on calculated fields, seeing as the main theme of that page is NOT to use calculated fields :)

casscar,

As gustav is suggesting, the way to do this is to have your FORM default to a due date that is 14 days in the future.  (I am presuming that, like many library systems, while there may be a default due date, the clerk has an ability to override that due date.)

Patrick
Top Expert 2016
Commented:
casscar,


* do NOT place a formula to calculate the Due_Date in the control source of the textbox for Due_Date field,  set the control Source for the textbox to the field Due_Date


*use the form to calculate the Due_date in the  afterupdate event of the textbox for Date_Issued

private sub date_Issued_afterupdate()

me.due_date=dateadd("d",14,me.date_issued)

end sub

Author

Commented:
Hi all,

Have tried all the options but it seems that i'm still doing something wrong. Attached is the database that I'm working with and I was trying to work on the below tasks, which apperently i ended up getting stuck in most of them.

Would appreciate if i can get any help.

In the book lending form

•      Book Code to be generated automatically as per table 1 book list, once the book title is selected.
•      Due Date to be equal to 2weeks from the date issued (inputted by the user when a book is borrowed)

General Queries:
•      To identify any overdue books
•      To identify the borrowing history of a specific book
•      To identify the borrowing history of a specific employee

One last thing please, I added a new field ‘currently borrowed’ in the book list table; I would like this update to appear in the relative form if possible please.

Hope you can help me

Regards
Top Expert 2016

Commented:
use this codes

Private Sub Date_Issued_AfterUpdate()
Me.[Due Date] = DateAdd("d", 14, Me.[Date Issued])
End Sub



Database-Testing.mdb

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for casscar's comment http:/Q_27243847.html#36333987

for the following reason:

k
Top Expert 2016

Commented:

casscar,

did you try the sample db uploaded at http:#a36334774 ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial