MichaelRed's FAQ181-261 1

[InfoNow]

Please help...trying to count number workdays between to dates. Using FAQ181-261, I was able to accomplished it, but now, I would like accuracy down to the number of hours. FAQ181-261 seems to round up/down a whole day. Is it possibe to calculate down to hours if the date fields have date and time in it( 4/29/2004 11:17:59 AM)? Of course, the no weekends/holidays rule still applies.

TIA

 

[AccessUser22]

Thanks for responding. I actually was able to do it myself creating something similar to what you have in the other thread.

Public Function GetDate(StartDate As Date, NumDays As Integer) As Date

'Get the date of the next workday that is a set amount of days from a start date

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

Dim MyDate As Date
Dim MyEndDate As Date
Dim ActualDays As Integer
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)

MyDate = Format(StartDate, "Short Date")
MyEndDate = Format(StartDate, "Short Date")

Do Until ActualDays = NumDays - 1

ActualDays = DeltaDays(MyDate, MyEndDate)
MyEndDate = MyEndDate + 1
If ActualDays = NumDays Then
Exit Do
End If

Loop

GetDate = MyEndDate

rstHolidays.Close
Set rstHolidays = Nothing
Set dbs = Nothing

End Function

Thanks again!

 

[MichaelRed]

A some what 'novel' approach, but not the most efficient.

SEveral 'elements' appear to be unused, such as:

[tab]rstHolidays
[tab](and, corresponndingly) dbs
[tab]NumSgn
[tab]strCriteria

And then, of course, instantiating 'DeltaDays' 20 or so times doesn't improve the performance.

I suggest that some additional thought be put into the process.





MichaelRed
mlred@verizon.net

 

[dhookom]

You can try this function:

Public Function GetFutureWorkDate(datStart As Date, intDays As Integer) As Date
    Dim dbs As DAO.Database
    Dim rstHolidays As DAO.Recordset
    Dim datTarget As Date
    Dim strCriteria As String
    Dim intDay As Integer
    intDay = intDays
    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)
    datTarget = datStart
    For intDay = 1 To intDays
        datTarget = datTarget + 1
        Select Case Weekday(datTarget)
            Case 1, 7
                intDay = intDay - 1
            Case Else
                strCriteria = "[HoliDate] = #" & _
                    Format$(datTarget, "yyyy-mm-dd") & "#"
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    'Do Nothing
                Else
                    intDay = intDay - 1
                End If
        End Select
    Next
    'close the objects and set to nothing
    rstHolidays.Close
    Set rstHolidays = Nothing
    Set dbs = Nothing
    GetFutureWorkDate = datTarget
End Function

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[AccessUser22]

Yes, some extra elements added up in there that I didn't end up using in the long run. I need to clean those out. I'll look into your code. Again, I needed to come up with something in a hurry for a report that needed to go out. I wasn't concerned at the time with highly efficient code. Thanks for the input...I'll look into your comments.