comparing dates against dates held in variants

[Mile-O]

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]

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]

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!

 

[Mile-O]

Surely the example I've posted contains a module and demonstrates how the function is called within a query?

 

[starlight]

dates

Hi,

Sorry I haven't been able to unzip the file and take a look yet. My reply (I thought) was to a post where you had just written the code!!

Thanks for your prompt reply anyway, I'll call again when I need more help!

Have a nice day



-------------------------------------------
St*rlight
Let the St*rs shine bright!

 

[Pat Hartman]

Take a look at this kb article:

HOW TO: Determine If a Date Falls on a Weekend or a Holiday in Access 2002

 

[Jon K]

My concern is as the database grows, ........ 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

When I put your code in the attached Access 2000 DB and ran the query, it did not throw up any error.

I also found that this line could work with or without the CDate() function:-
If (CDate(varDates) = dtmCurrent) Then blmIsHoliday = True
If varDates = dtmCurrent Then blmIsHoliday = True

The code was fast. On the WinMe system I used, it took less than two seconds to return the query results for 1500+ records.