Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Generalized Holidays

Posted by: azolder Oct 13 2015, 03:47 PM

I appears to me holidays are traditionally handled in Access by someone inputting the holiday name and date into the database, for a number of years. Computation accuracy are limited to dates input.

This database demonstrates a mechanism to perpetually generate holiday listings based on their definitions, limited only by the years in tblYears, which is much easier to maintain than a table of individual holidays. Changing company policy and legal holidays are accommodated by including a range of effective dates.

Holiday types supported are:


The handling of weekends is another sticky point. Typically they are hard coded into applications, and assume typical Saturday/Sunday holidays. But weekends are cultural and mater of company policy.

This database demonstrates a mechanism to accommodate different weekend day scenarios, including accounting for changes.

Weekends also bring up the issue of holiday observation. There are three classes of holiday observation


 HolidaysXX.zip ( 226.65K ): 159

Posted by: Digilion Feb 4 2016, 07:50 AM

Hi,

the Easter module computes wrong dates. It reports Easter dates one week earlier of actual dates.

Posted by: azolder Feb 4 2016, 02:23 PM

I was sure I verified my formulas, but yes, the posted database has that error. The problem was tracked down and corrected with this revised database:
 HolidaysX1.zip ( 141.9K ): 46

Posted by: Digilion Feb 5 2016, 04:05 AM

Hi,

the Easter dates are now correct, but there are errors about the other Holidays related to Easter. See Ashes Day; for this year it reports a day in May when it falls in February 10.

Posted by: azolder Feb 5 2016, 06:01 AM

Two steps forward, one step back. The EasterVBA function in the modEasterAdventDow procedure needs to be corrected. Add the offset instead subtracting it.

CODE
'/************************
' EasterVBA
'  determines the easter date with offset based on the assa procedure
'ARGUMENTS
'  lYear - The desired year
'  Offset (optional)  the offset for a specific date based on easter,
'     e.g. -46 (46 days before easter) for ash wednesday
'NOTE
'  4 Feb 16: this procedure replaces the one in the Oct 13 2015 version
'  5 Feb 16: corrected offset computation
'**********************************************/
Function EasterVBA(lYear As Long, Optional offset As Long = 0) As Date
   Dim m As Long, d As Long
  
   'determine the date using the assa procedure
   EasterDate d, m, lYear
  
   EasterVBA = DateSerial(lYear, m, d) + offset
  
End Function

Posted by: LuigiSoft Feb 6 2016, 03:42 PM

Hi,
Thank you for this useful database.

Having problems after calling up the frmHolinput Form.
Klicking the mouse on any field in the Select Holiday List brings up a runtime error 2147220499.
On debug in the Public Function WeHolOffset(dteTest As Date) the following code lines are highlighted:

Err.Raise vbObjectError + 1005, "WeHolOffset", _
"Lookup error on: " & dteTest

The following references are set (Access 2010):

Visual Basic for Applications
Microssoft access 14.0 object library
OLE Automation
Microsoft 14.0 access database engine object.

Could this problem be attributed to the date format.
I am in Germany and here we use the european date format, for example 01.01.2016, not 01/01/2016.
Is there a way around this problem? Would I have to change the code (Const conNoDate As Date = #1/1/1000#) e.g. to #01.01.1000# or the date format in the tables?

Appreciate any pointers you can give to solve the problem.

Posted by: azolder Feb 6 2016, 05:40 PM

What is the actual error message given? It should be displayed.

Supposedly date constants are supposed to be in the US format (#mm/dd/yyyy#).
To test whether it's the date format, try setting the constant to the following

CODE
Const conNoDate As Date = -328716#    '#1/1/1000#

As conNoDate has to be a constant, we're substituting the double equivalent of January 1, 1000.

Posted by: LuigiSoft Feb 7 2016, 02:52 PM

Thank you for your quick reply.

I changed the code line as suggested. The problem reported still persists.

I noted that when selecting 'Chanuka begins' from the list, I do not get an error message. The pre selected item changes without problem from the current record to 'Chanuka begins' and all associated data appears on the form.
Also, when the error box pops up and I close it without debugging, the list item I selected then appears in the fields on the right of the form.

Please see attached zip file for the error code and an image of the form!
Thank you again!

 Runtime_error.zip ( 72.61K ): 23
 

Posted by: azolder Feb 7 2016, 06:06 PM

I've traced the error to the International/Regional date format settings. I'm updating the database to correct those problems, as well as other issues I've encountered.

Posted by: azolder Feb 7 2016, 07:10 PM

An updated version of the Generalized Holidays demo database is attached. Issues from users were addressed, as well as other issues encountered during testing. I've tested it in Access 2007 and 2013 and believe I've found most of the issues. Please inform me of any problems you may have using it.  Holidays02.zip ( 178.13K ): 30

Summary of Changes:


Original Description:
This database demonstrates a mechanism to perpetually generate holiday listings based on their definitions, limited only by the years in tblYears, which is much easier to maintain than a table of individual holidays. Changing company policy and legal holidays are accommodated by including a range of effective dates.

Holiday types supported are:

The handling of weekends is another sticky point. Typically they are hard coded into applications, and assume typical Saturday/Sunday holidays. But weekends are cultural and mater of company policy.

This database demonstrates a mechanism to accommodate different weekend day scenarios, including accounting for changes.

Weekends also bring up the issue of holiday observation. There are three classes of holiday observation

Posted by: LuigiSoft Feb 8 2016, 08:58 AM

Hi,

Thank you for the solution. using the 02 version, I got an error when selecting items in the list, attributed to a problem in the Easter function.

I changed the first line of the code from

Function EasterVBA(lYear As Long, Optional offset As ehEasterHolidays = 0) As Date

to

Function EasterVBA(lYear As Long, Optional offset As Long = 0) As Date

All works fine now.

Many thanks.

Posted by: azolder Feb 8 2016, 09:53 AM

Dang. I'd corrected that. I must have ... oh ... I must have zipped it before catching it. Guess I have a problem with version control. shrug.gif

How is the rest of it working for you?

I'm working on a couple of improvements. The correction will be included in that update.

Posted by: azolder Feb 9 2016, 12:56 PM

This demo database demonstrates a mechanism to perpetually generate holiday listings based on their definitions, limited only by the years in tblYears, which is much easier to maintain than a table of individual holidays. Changing company policy and legal holidays are accommodated by including a range of effective dates.
 Holidays021.zip ( 192.38K ): 109

Holiday types supported are:


The handling of weekends is another sticky point. Typically they are hard coded into applications, and assume typical Saturday/Sunday holidays. But weekends are cultural and mater of company policy.

This database demonstrates a mechanism to accommodate different weekend day scenarios, including accounting for changes.

Weekends also bring up the issue of holiday observation. There are three classes of holiday observation

Summary of Changes:
.

Posted by: LuigiSoft Feb 12 2016, 02:38 PM

Everything is working fine now.

I had to change the reference to the excel object library from excel 15.0 to 14.0, as I am running office 2010.
Many thanks again for your help.

Posted by: azolder Feb 12 2016, 04:07 PM

Great!

My pleasure.
cheers.gif