# Thread: Calculating Dates in an Access database

1. Registered User
Join Date
Aug 2004
Posts
48

## Unanswered: Calculating Dates in an Access database

I'm looking for a formula, expression or event procedure that will allow me to calculate a future date based on a given number of weeks. (The user will select the start date and the desired number of weeks). The weeks must be Monday through Friday only and exclude certain holidays that will be entered in the database.

Can someone help?

2. Registered User
Join Date
Mar 2007
Posts
277
Have you tried creating your own with the DateAdd() function? What is your definition of a week? If the ending date falls on a Friday and that is a Holiday, what do you do?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
how do you know what dates are holidays or non working days?
having worked that out how does your computer know what are non working days?

if its simply a date n weeks in advance then as RuralGuy suggests dateadd's yer man.
if you want to take account working days then you almost certainly need to define a working calendar.

There are lots of implementations

4. Registered User
Join Date
Aug 2004
Posts
48

## Calculating Date in an Access database

In response to the replies from RuralGuy and heraldem:

The following is code I found that I've used in an Access database to calculate a future date from a specified starting date. The code is attached to an on-click event procedure of a command button.
The user selects a date from a calendar control, clicks on the command button and is then asked how many days from the starting date the future date should be. The result is given after the user clicks OK. (The code can a be modified to get results for weeks or months.)

Private Sub Command 18_Click()
Dim FirstDate As Date 'Declare variables
Dim IntervalType As String
Dim Number As Integer
Dim Msg
IntervalType = "d" '"d" specifies days as interval
FirstDate = Calendar1
Number = INputBox ("Enter number of days to add")
Msg = "New date: " & DateAdd(IntervalType, Number,FirstDate)
MsgBox Msg
Exit_Command18_Click
End Sub

The question is: how do I adapt,modify or change the code so that weekends and specified holidays are excluded in the calculation? I'm new at this so I need all the help I can get. Thanks.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
same comment as before
how do you know what is a non working day?
how do you then translate that into something that the application can resolve?

in practice the only solution I've ever found to this problem is to have a working calendar which clearly defines what are working days, or what aren't.

6. Registered User
Join Date
Dec 2006
Location
Momence, IL USA
Posts
56
I notice your code looks very similar to the example in the help file for the DateAdd function. That's a pretty crappy example, even by MS standards. (No offense)
Your situation is interesting because you have to move forward, and you can't tell how many days in the future will contain the required number of "working days". I tried to find a way to do it with one sql query (parameter query), but you can't set the "TOP" clause with a parameter.
This forum discourages the use of cursors (looping through records); that's my excuse for the ugly sql string in the first sub. But the second, cursor-based sub is probably preferable.

Let's assume you have a table named "holidays" with one field "holiday". It's a date/time field containing holiday dates such as 7/4/2007. The year portion (and time portion) of the holiday dates doesn't matter because we'll use the format function to extract only the month/day part (e.g. Jul-04) for comparison.
For this first sub, you'll also need a table named "ints" with one field "i" containing consecutive integers (0 through 1000 will handle a final date almost 3 years after the startdate).
An Access form is used with two buttons, named cmdSql and cmdCursor, and two textboxes named txtNumdays and txtStartdate. I know you said you have a calendar control to input the startdate, so you can substitute that for txtStartdate.
Here's the code for cmdSql:

Code:
```Private Sub cmdSql_Click()
Dim rs As New Recordset
Dim sqlstr As String, numdays As String
Dim startdate As String, q As String
q = Chr\$(34)

Set rs.ActiveConnection = CurrentProject.Connection
txtNumdays.SetFocus
numdays = txtNumdays.Text
txtStartdate.SetFocus
startdate = "#" & txtStartdate.Text & "#"

sqlstr = "select max(workdays) as FinalDate from " _
& "(select top " & numdays & " dateadd(" & q & "d" & q & ",ints.i," _
& startdate & ") as workdays from ints where " _
& "weekday(dateadd(" & q & "d" & q & ",ints.i," & startdate & "))<>1 " _
& "and weekday(dateadd(" & q & "d" & q & ",ints.i," & startdate & "))<>7 " _
& "and format(dateadd(" & q & "d" & q & ",ints.i," & startdate & ")," & q & "mmm-dd" & q & ") " _
& "not in (select format(holiday," & q & "mmm-dd" & q & ") from holidays))"

MsgBox rs(0).Value
rs.Close
End Sub```
There is no error-checking in this or the next code example. Notice in the sqlstr the 1 and 7. You can replace those with vbSunday and vbSaturday, or whatever days you consider "weekend" days.

The following code for cmdCursor doesn't require an ints table, only your holidays table. It also runs faster in my tests. It figures dates the same way you would manually; go forward from the startdate, only counting the days that are not weekends or holidays. Again, no error-checking in this code:

Code:
```Private Sub cmdCursor_Click()
Dim rs As New Recordset
Dim joinstr As String
Dim sqlstr As String, startdate As String
Dim i As Integer, numdays As Integer, workdays As Integer
Dim currdate As Date
q = Chr\$(34)

Set rs.ActiveConnection = CurrentProject.Connection
sqlstr = "select holiday from holidays"

Do While Not rs.EOF
joinstr = joinstr & Format(rs("holiday").Value, "mmm-dd") & ","
rs.MoveNext
Loop
rs.Close

txtNumdays.SetFocus
numdays = CInt(txtNumdays.Text)
txtStartdate.SetFocus
startdate = txtStartdate.Text

i = 1
Do While workdays < numdays
If Weekday(currdate) <> vbSaturday And Weekday(currdate) <> vbSunday Then
'make sure currdate is not a holiday
If InStr(1, joinstr, Format(currdate, "mmm-dd")) = 0 Then
workdays = workdays + 1
End If
End If
i = i + 1
Loop
MsgBox currdate

End Sub```
Both examples seem to produce the correct output. It is a pain counting a bunch of days ahead on a calendar, so I'll leave the in-depth testing up to you (if you ever come back to this thread).

7. Registered User
Join Date
Apr 2007
Posts
42

## I'm probably taking too simplist of an approach, but...

Elizabeth,
Could you simply take the start date and add the # of weeks times 7 to it to get your end date?
There is also another function that I've used called DatePart that helps you know what day of the week a date is: DatePart("w", dteCurrDate, vbMonday) = 5 Then 'it is Friday. That same function can check for holidays too. Check it out and see if it does what you need.
thanks, Terri

8. Registered User
Join Date
Apr 2007
Posts
42

## oops, my mistake

oops, my mistake ... DatePart does not tell you if it is a holiday or not. I thought I had read that somewhere when I was searching to determine the day of week. Sorry.

9. Registered User
Join Date
Dec 2006
Location
Momence, IL USA
Posts
56
Terri,
This is off-topic, but I'm curious about that variable named dteCurrDate in the DatePart() example you posted.
I suppose it's a Hungarian naming convention. But what does the dte stand for? Date-Time something? What is the "e" for? Maybe it's just the word "date" with the "a" omitted. I googled it, and there were hundreds of hits on "dteCurrDate". It's like a word puzzle to me now.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
I still don't see how you propose to identify what is a holiday
As a simple approach you could probably argue that weekends are non workign days, or maybe there are 5 working days in a week.

But no computer system is going to 'know' what are your organisation's holidays are. I think there is a function somewhere within VB/VBA that knows about some public holidays (eg Christams and so on, but not all public holidays, or even all holidays for your organisation or its employees.

So things like datepart, datediff etc arent' going to cut it.

I make the same comment as before how do you propose to identify what are holidays, having done that how do you think that should be integrated into your application?

11. Registered User
Join Date
Apr 2007
Posts
42
a-dam,
dteCurrDate is a VBA variable named as such using a naming convention whereby dte signifies that it is a date field - I try to stick with 3-letters to represent the type of object that I'm naming. dteCurrDate signifies that this field is being set to the current date earlier in the code.
thanks, Terri

12. Registered User
Join Date
Apr 2007
Posts
42
healdem,
One solution would be to include a table of holidays which fall on Monday through Friday on the calendar. This can be maintained at the beginning of each year (esp. since company holidays can sometimes change from year to year). Once you determine that a date is between Monday and Friday, just do a check against this table to make sure there are no hits, and you know it is a workday.

13. Registered User
Join Date
Dec 2006
Location
Momence, IL USA
Posts
56
I know it's a naming convention signifying a date data type, probably a microsoft "suggested" prefix. I still wonder about the "e" in "dte". Maybe "dat" was already taken. Why not dtWhatever (for date/time)? I guess it's because "that's the way we've always done it".
But anyway, as healdem and others were suggesting, instead of putting holidays in a table and coding for weekends, you might as well put the weekends in the table too. Then again, a more positive approach would be to have the table contain working days (the dates that aren't weekends or holidays). If you get all your workdays set up in the table (ordered by date), and then add a sequential (autonumber) field, you can use this:

Code:
```SELECT workday
FROM workdays
WHERE auto = (SELECT Min(auto) AS minauto
FROM workdays
WHERE workday >= [enter date]) +  [enter number];```
When the time comes to modify your workdays table (basically every year), then you could remove the autonumber field, enter the workdays, add autonumber field.
Last edited by a-dam; 12-04-07 at 16:20.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•