Results 1 to 13 of 13
  1. #1
    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. #2
    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?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #4
    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.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #6
    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))"
    
    rs.Open sqlstr, , adOpenKeyset, adLockOptimistic, adCmdText
    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"
    rs.Open sqlstr, , adOpenKeyset, adLockOptimistic, adCmdText
    
    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
        currdate = dateadd("d", i, startdate)
        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).
    Don't Bogart That Squishee!

  7. #7
    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. #8
    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. #9
    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.
    Don't Bogart That Squishee!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #11
    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. #12
    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. #13
    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.
    Don't Bogart That Squishee!

Posting Permissions

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