Date Calculation

[Guest]

I'm performing date calculations in my forms and reports and I'm having
trouble getting the correct results. I need to determine a future date based
on a calculation. For example the <date field> + 5. I've done this type of
calculation before that's not the problem.

What is different this time is I can only count business days and have to
exclude holidays, and what the military refers to as down days when counting
to this future date.

The dates to omit are in a table with the date as the key a reason why that
date is excluded holiday, down day, etc.

I'm thinking a loop that works something like this:

1. Set a counter to one
2. advances the start date by 1
3. checks the exclusions table to compare against the dates stored
4. Does the date have a match in the table
4a Yes there was a match go back to #2
4b No, there was no match continue to #5
5. Is the day a Saturday or Sunday
5a Yes it is a Saturday or Sunday go back to #2
5b No, increase the counter by 1
6. Is the counter equal to 5 (from the example above)
6a Yes return the date calculated
6b No, advance the counter and go to #2

I just do not know the syntax necessary to write VBA function to handle
this. Is there a site somewhere with code examples? Little Help Please.

 

[SteveS]

Check out this link to see if it will help you:

http://mvps.org/access/datetime/date0006.htm


If you still need help, post back.


HTH

 

[SteveS]

OK, on 2/27 I wrote a function for TwinDad to find the Previous Business Date.
So here is a function for a future date.

Watch for line wrap.....

'****** beg code ***********
Public Function FutureDate(StartDate As Date, BusinessDays As Integer) As Date

On Error GoTo Err_FutureDate

Dim intKount As Integer
Dim rst As DAO.Recordset
Dim strSQL As String

If BusinessDays < 1 Then
MsgBox "Number of business days must be positive and greater than zero"
FutureDate = StartDate
Exit Function
End If

'open Exclusions recordset
strSQL = "SELECT [ExclusionDate] FROM tblExclusions"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

StartDate = StartDate + 1
intKount = 0

Do
If Weekday(StartDate) <> vbSaturday And Weekday(StartDate) <> vbSunday Then
rst.FindFirst "[ExclusionDate] = #" & StartDate & "#"
If rst.NoMatch Then
intKount = intKount + 1
End If
End If

If BusinessDays = intKount Then
Exit Do
End If

'inc date
StartDate = StartDate + 1
Loop

'return future business date
FutureDate = StartDate

Exit_FutureDate:
rst.Close
Set rst = Nothing
Exit Function

Err_FutureDate:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_FutureDate
End Select

End Function

'****** end code ***********


HTH

 

[Guest]

Steve,

Thanks for the guidance! I try implementing the solution this weekend
thanks for the starting point!
--
Mark H. Trainor

OK, on 2/27 I wrote a function for TwinDad to find the Previous Business Date.
So here is a function for a future date.

Watch for line wrap.....

'****** beg code ***********
Public Function FutureDate(StartDate As Date, BusinessDays As Integer) As Date

On Error GoTo Err_FutureDate

Dim intKount As Integer
Dim rst As DAO.Recordset
Dim strSQL As String

If BusinessDays < 1 Then
MsgBox "Number of business days must be positive and greater than zero"
FutureDate = StartDate
Exit Function
End If

'open Exclusions recordset
strSQL = "SELECT [ExclusionDate] FROM tblExclusions"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

StartDate = StartDate + 1
intKount = 0

Do
If Weekday(StartDate) <> vbSaturday And Weekday(StartDate) <> vbSunday Then
rst.FindFirst "[ExclusionDate] = #" & StartDate & "#"
If rst.NoMatch Then
intKount = intKount + 1
End If
End If

If BusinessDays = intKount Then
Exit Do
End If

'inc date
StartDate = StartDate + 1
Loop

'return future business date
FutureDate = StartDate

Exit_FutureDate:
rst.Close
Set rst = Nothing
Exit Function

Err_FutureDate:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_FutureDate
End Select

End Function

'****** end code ***********


HTH