Microsoft Access Date Calculation

  • Thread starter Thread starter Marko
  • Start date Start date
M

Marko

I am currently setting up a library database and need to
know the expression for calculating the day a book is due
for return. A book is always due back one calander month
after the date the book was borrowed. If the due date
falls on a saturday or sunday then the due date has to
become a Monday. Can anyone help.
 
Marko,

Try this...
DateAdd("m",1,[Borrowed])+IIf(Weekday(DateAdd("m",1,[Borrowed]),2)<6,0,8-Weekday(DateAdd("m",1,[Borrowed]),2))
 
A month from now is DateAdd("m", 1, Date())

The day of the week can be determined using the Weekday function.

The following untested aircode should do what you're looking for:

Function AddOneMonth(DateIn As Date) As Date
' Adds one month to a date.
' If the resultant date is a Saturday or Sunday,
' the function returns the following Monday.

Dim dtmCalculated As Date

dtmCalculated = DateAdd("m", 1, DateIn)
Select Case Weekday(dtmCalculated)
Case vbSaturday
dtmCalculated = DateAdd("d", 2, dtmCalculated)
Case vbSunday
dtmCalculated = DateAdd("d", 1 dtmCalculated)
End Select

AddOneMonth = dtmCalculated

End Function
 
Try the following

Private Function DueDate(ByVal BorrowedDate As Date) As Date
Dim intWeekday As Integer
Dim dteDue As Date

dteDue = DateAdd("m", 1, BorrowedDate )
intWeekday = Weekday(dteDue)
If intWeekday = vbSaturday Then
dteDue = DateAdd("d", 2, dteDue)
ElseIf intWeekday = vbSunday Then
dteDue = DateAdd("d", 1, dteDue)
End If

DueDate = dteDue
End Function

Hope This Helps
Gerald Stanley MCSD
 
I am currently setting up a library database and need to
know the expression for calculating the day a book is due
for return. A book is always due back one calander month
after the date the book was borrowed. If the due date
falls on a saturday or sunday then the due date has to
become a Monday. Can anyone help.
Click to expand...

Any chance you could get the policy changed so that it's *four weeks*
later (i.e. always the same day of the week)? <g>

If not:

DateAdd("m", 1, [CheckOutDate]) + IIF(WeekDay(DateAdd("m", 1,
[CheckOutDate]), vbMonday) > 5, 8 - WeekDay(DateAdd("m", 1,
[CheckOutDate]), vbMonday), 0)

Air code, untested...
 
Marko,
Simple. If you're using it as a form field, or a
report, you can set the record source as a calculation
based on the date borrowed, such as: "=[DateBorrowed]
+30" which will take the original value of the
DateBorrowed field, and add 30 days to it. Specifying
exactly one month, instead of 30 days, requires you to
change the format of the field to mmyy, then adding 1
instead, but you'll lose the day date value. If you want
to have this someplace and use it in many different forms
or reports, instead create a query, being sure to pull the
DateBorrowed field into the query, and next to it, enter
the same thing in the Field row in the Query grid; Access
will automatically change it to enter Expr1: prior to your
calculation notation (the = sign), and you can then
highlight the Expr1, and change it to whatever you want,
such as DateDue.
Bernie
 
Back
Top