comparing dates against dates held in variants

sametch · Jul 25, 2003

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 · Jul 25, 2003

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

sametch · Jul 25, 2003

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 · Jul 25, 2003

Where is the mismatch error occuring?

sametch · Jul 25, 2003

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 · Jul 25, 2003

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 · Jul 25, 2003

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 · Jul 25, 2003

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

dcx693 · Jul 25, 2003

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 · Jul 25, 2003

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 · Jul 25, 2003

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 · Jul 26, 2003

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 · Jul 26, 2003

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

Mile-O · Jul 28, 2003

Here you go:

Code:

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

Code:

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

Code:

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 · Jul 28, 2003

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

sametch · Jul 29, 2003

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?

sametch · Jul 30, 2003

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

Mile-O · Jul 30, 2003

Here you go:

British Day Count

This little database allows you to count the number of days between two dates and it excludes weekends, bank holidays, Good Friday, Easter Monday, Christmas, Boxing Day, and New Year's Day.

It also allows you to add custom holidays which can then be excluded from the count of the number of days.

The use of it all should be self explanatory as it's all form based.

Mile-O · Jul 30, 2003

I did a second test on my Database by making a quick bit of code that fills a table with 3,000 records with two fields: StartDate and EndDate. StartDate was, in each case: 1st jan, 2003 and the end date was randomly selected between 1 and 300 days after the first of the year. Then I made a query that performs the calcualtion and it took about 4 or 5 mins on my machine - which was quicker than I expected.

I then changed it a little to the numbers you mentioned: 1,500 records and an average difference of 20 days between dates and it was queried rather quickly.

This version will generate a table of 1,500 records as it opens with an average count of 20 days. Just hit the Open Query button to see how long it takes.

Try adding custom holidays and open the query again.

starlight · Sep 19, 2003

Calculate Working Days

Hi Mile-O-Phile,

I'm a little new to this so please expalin how and where I would put the code?

~Does the code need to be put into a module?
~Do I put all of it in the same module or create 3 separate ones?
~How do I call the function from a query?

Your help most appreciated.

-------------------------------------------------------
St*rlight
Let the St*rs Shine Bright!

comparing dates against dates held in variants

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Back once again...

Registered User.

Back once again...

Registered User.

Registered User.

Registered User.

Back once again...

Attachments

Back once again...

Attachments

starlight

Guest

Similar threads

Users who are viewing this thread