comparing dates against dates held in variants (1 Viewer)

[sametch]

I have an array which holds dates as variants and I want to compare each with "real" dates held in a date field.

My routine keeps throwing up a data missmatch error. Is there anything I can do to make them both look the same for a successful comparison.

 

[dcx693]

Use the CDate function to convert the variants to "real" dates.

 

[sametch]

That doesn't seam to work. I must be doing something wrong. what I am trying to do is load a table of holiday dates into an global array. Then use this array as a check list to work out the number of working days between two dates.

My code is as follows:

***************Function to create array*****************

Public Function GetHolidays() As Boolean

Dim rstHolidays As ADODB.Recordset

On Error GoTo Err_GetHolidays

Set rstHolidays = New ADODB.Recordset

With rstHolidays

.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open "tblHolidays"

If .EOF And .BOF Then
GetHolidays = False
Exit Function
Else
'put the dates into the array

varHolidays = .GetRows(, , "HolidayDate")
.Close
End If

End With

Set rstHolidays = Nothing

GetHolidays = True

Exit_GetHolidays:

*****************Function to compare dates*************

Public Function CountWorkingDays(ByVal varStartDate As Variant, ByVal varEndDate As Variant)

Dim intCounter As Integer, intTotal As Integer
Dim blmIsHoliday As Boolean
Dim varDates As Variant
Dim dtmCurrent As Date
intTotal = 0

For intCounter = 0 To (varEndDate - varStartDate)

dtmCurrent = (varStartDate + intCounter)

If (Weekday(dtmCurrent) = 1) Or (Weekday(dtmCurrent) = 7) Then
'Do Nothing
Else

blmIsHoliday = False
For Each varDates In varHolidays
If (CDate(varDates) = dtmCurrent) Then blmIsHoliday = True
Next varDates

If Not (blmIsHoliday) Then intTotal = intTotal + 1

End If

Next intCounter

CountWorkingDays = intTotal

End Function

 

[dcx693]

Where is the mismatch error occuring?

 

[sametch]

The function GetHolidays works fine!

The problems Occur in the Function CountWorkingDays in the For each loop when the following line is excuted:

If (CDate(varDates) = dtmCurrent) Then blmIsHoliday = True

I tried to debug it with msgBox VarType(VarDates) and msgBox(dtmCurrent) but even this caused a data mismatch error.

 

[dcx693]

I don't have a lot of experience working with arrays, but I'm wondering if the problem might be in in the loop itself. Do you know that the For...each structure works with the array? How about looping through the array using The LBound and UBound of the array? I'm wondering if varDates isn't being treated as an array rather than an element of the varHolidays array.

 

[sametch]

dcx693

The for each structure works fine, i used a test routine with it to validate the array contents. I like you don't have much experience of arrays, an attempt to use Lbound and UBound failed.

The main reason i am using an array, is I am using the function in a query to count the working days between two dates. I figured if i had to iterate holiday dates in another table for each day between the two dates. And bearing in mind I need to do it for every record. I fear as the database grows, it will grind to a halt.

My thought process is by storing the holiday dates in a global array variable, the access time should be much quicker (I am happy to be corrected)

I like you am suscipcious of how the varDates variable is being treated. Thats why I tried to use "MsgBox VarType(varDates)" to find out, but that cuased a datatype missmatch .

I am happy to try most ideas.

Sametch

 

[dcx693]

That's weird about VarType(varDates) getting you a data type mismatch. Is the varHolidays a one dimensional array?

 

[dcx693]

sametch, would you happen to have a copy of Excel? If so, investigate the use of the Networkdays() function. You can call that function from Access.

 

[sametch]

dcx693

Is that a standard function? I can't seam to find it. I have Excel 2000 and I have looked in the entire list of standard functions, but its not there. I have also looked in vidual basic help and can't find anything on it!

 

[dcx693]

Ack. Sorry, forgot to mention that it's part of the Analysis Tookpack. Go to Tools, Add-ins, and click on Analysis Tookpack. (Don't know if you'll need Analysis Tookpack - VBA, but if you'll be calling it from Access using VBA, probably.)

Of course, that assumes you installed the Analysis tookpack as part of the Excel installation.

 

[Mile-O]

Rather than having the dates in a table and stored in an array, you could write a function that calculates the date of holidays - and excludes them - within the time period given.

I have written a function that works for counting the number of days between two dates excuding weekends and British bank holidays.

I've looked up American holidays and they seem to follow the same pattern - either set dates or holidays that occur on the first Thursday* of a certain month.

And I'd guess that holidays throughout the world were all easily calculable.



* Don't quote me on that; might be third Wdnesday of a month. Can't remember.

 

[sametch]

Mile-O-Phile

We are based in the UK so I would be interested in seeing your solution for calculating bank holidays. I wasn't sure if it was possible, because I didn't think they followed any particular pattern.

I would still prefer to have holidays in a table, becuase themn it would allow for the different xMas holidays our company has a habit of working (sometimes whole weeks off, other times 2 days off!). But If I can't get my solution working. Standard bank hols would be better than nothing.

Sametch

 

[Pat Hartman]

The only holiday that is difficult to calculate is Good Friday and I don't think many companies observe it. I don't remember the rules for calculating when Easter occurs but they are somewhat unusual as I recal.

In the US, some holidays such as Christmas, New Year's Day, and Independence Day (July 4) turn into two-day holidays depending on which day of the week they fall on. When the holidays fall on Tuesday or Thursday, the Monday or Friday are also celebrated by most companies as a holiday. Banks don't take two-day holidays though, just businesses.

 

[Mile-O]

Here you go:

Public Function CountDays(ByVal dteStartDate As Date, dteEndDate As Date) As Integer
 
    'On Error GoTo Err_CountWeeks
    
    Dim dteTemp As Date, intDays As Integer
    
    dteTemp = dteStartDate
    
    Do While dteTemp <> dteEndDate + 1
        
        Select Case WeekDay(dteTemp)
            Case Is = 1, 7
                ' do nothing
            Case Else
                Select Case dteTemp
                    Case Is = DateSerial(Year(dteTemp), 1, 1), _
                        DateOfEaster(Year(dteTemp)) - 2, _
                        DateOfEaster(Year(dteTemp)) + 1, _
                        GetBankHoliday(DateSerial(Year(dteTemp), 5, 1)), _
                        GetBankHoliday(DateSerial(Year(dteTemp), 5, 25)), _
                        GetBankHoliday(DateSerial(Year(dteTemp), 8, 25)), _
                        DateSerial(Year(dteTemp), 12, 25), _
                        DateSerial(Year(dteTemp), 12, 26)
                        ' do nothing
                    Case Else
                        intDays = intDays + 1
                End Select
        End Select
        dteTemp = dteTemp + 1
    Loop
    
    CountDays = intDays

Exit_CountDays:
    Exit Function

Err_CountWeeks:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CountDays
        
End Function

Public Function DateOfEaster(ByVal intYear As Integer) As Date

    On Error GoTo Err_DateOfEaster
 
    Dim intDominical As Integer, intEpact As Integer, intQuote As Integer
    
    intDominical = 225 - (11 * (intYear Mod 19))
    
    ' if the Dominical is greater than 50 then subtract multiples of 30 until the resulting
    ' new value of it is less than 51
    If intDominical > 50 Then
        While intDominical > 50
            intDominical = intDominical - 30
        Wend
    End If
    
    ' if the Dominical is greater than 48 subtract 1 from it
    If intDominical > 48 Then intDominical = intDominical - 1
    
    intEpact = (intYear + Int(intYear / 4) + intDominical + 1) Mod 7
    
    intQuote = intDominical + 7 - intEpact
    
    ' if the quote is less than 32 then Easter is in March
    ' if the quote is greater than 31 then the quote minus 31 is its date in April
    If intQuote > 31 Then
        DateOfEaster = DateSerial(intYear, 4, intQuote - 31)
    Else
        DateOfEaster = DateSerial(intYear, 3, intQuote)
    End If

Exit_DateOfEaster:
    Exit Function

Err_DateOfEaster:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_DateOfEaster

End Function

Public Function GetBankHoliday(ByRef dteBankHoliday As Date) As Date

    On Error GoTo Err_GetBankHoliday

   Dim intCounter As Integer
    For intCounter = 0 To 6
        If WeekDay(dteBankHoliday + intCounter) = 2 Then
            dteBankHoliday = dteBankHoliday + intCounter
            Exit For
        End If
    Next intCounter
    GetBankHoliday = dteBankHoliday

Exit_GetBankHoliday:
    Exit Function

Err_GetBankHoliday:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_GetBankHoliday

End Function

 

[sametch]

Thanks Mile-O-Phile I will give that a try.

I will also continue to try to find a solution to what I am trying to acheive. If i do I will post it.

My latest thoughts are, would it be possible to create a global recordset veriable to store the dates in. At least that way each record would not have to run a query to create the recordset over and over again!

Sametch

 

[Pat Hartman]

If you want your holiday dates to be in a table, you could write a sub that calculates the dates for the next year and appends them to a table. Run the routine once a year.

Leave it to Mile to come up with the Easter rules Thanks.

 

[sametch]

This post is probably getting a little long, which I am not knocking because some useful stuff has come out of it.

But if I take it back to the start the real issue I have is this:

For a table which has a list of start and end dates (there could be thousands of them) I need to be able to check the number of working days between the two dates for every record. Where working days equals any day that is not a weekend, a public holiday OR a user definable holiday.

The only way I can see of acheiving this is to have a holiday table to hold holidays and then check each record in the main table against each record in the holiday table.

My concern is as the database grows, this will take an enormous amount of time to run. Hence why at the start I thought it would be quicker to load all holiday dates from the holiday table into an array.

But I can't make this work. Because of a data missmatch

Does anyone else have any alternative suggestions?

 

[Pat Hartman]

I have not had to solve this problem so I don't have a coded solution. But, what I would do is to use DateDiff() to find the absolute number of days between two dates. Then I would run a count(*) query to count the number of holidays between the two dates. Then I would count the number of weekend days between the two dates and subtract both of those numbers.

I have seen coded solutions posted here. Have you searched for any?

 

[sametch]

I have spent quite a lot of time searching. I have found solutions for not counting weekends, solutions for not counting standard "bank" holidays. But I am yet to see a solution for user defined holidays.

My solution is certainly too slow, with 100 records and an average difference between start date and end date of 20 days. the holiday table recordset has to be accesses 2000 times by the query and it takes many minutes to run. The table is likely to have 1500 records in the end not 100 so it will be an overnoight job.

The only way I can currently think of overcoming this is to create a new field in the master table for working days and populate it automatically when the record is amended. but i don't really like this, because if the holiday dates have been entered wrong and are subsequently changed this data would be innacurate!

Not an easy one this!

sametch