UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Generalized Holidays, Access 2007    
 
   
azolder
post Oct 13 2015, 03:47 PM
Post#1



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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:
  • Gregorian Day of Month
  • Gregorian Nth Weekday of Month
  • Easter and Easter Offsets
  • Advent Sundays
  • Hebrew/Jewish Day of Month
  • Hijri/Arabic/Islamic Day of Month

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
  • None - The holiday is not observed, that is there is no 'day off' for it.
  • Weekdays Only - The holiday is observed (a day off) only on weekdays. It is not observed on weekends.
  • Nearest Weekday - The holiday is observed. When it falls on a weekend it is observed on the "nearest workday".


Attached File  HolidaysXX.zip ( 226.65K )Number of downloads: 159
Go to the top of the page
 
Digilion
post Feb 4 2016, 07:50 AM
Post#2



Posts: 11
Joined: 12-September 02



Hi,

the Easter module computes wrong dates. It reports Easter dates one week earlier of actual dates.
Go to the top of the page
 
azolder
post Feb 4 2016, 02:23 PM
Post#3



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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:
Attached File  HolidaysX1.zip ( 141.9K )Number of downloads: 46
Go to the top of the page
 
Digilion
post Feb 5 2016, 04:05 AM
Post#4



Posts: 11
Joined: 12-September 02



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.
Go to the top of the page
 
azolder
post Feb 5 2016, 06:01 AM
Post#5



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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
Go to the top of the page
 
LuigiSoft
post Feb 6 2016, 03:42 PM
Post#6



Posts: 75
Joined: 13-May 03
From: NEAR FRANKFURT; GERMANY


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.
Go to the top of the page
 
azolder
post Feb 6 2016, 05:40 PM
Post#7



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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.
Go to the top of the page
 
LuigiSoft
post Feb 7 2016, 02:52 PM
Post#8



Posts: 75
Joined: 13-May 03
From: NEAR FRANKFURT; GERMANY


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!
Attached File(s)
Attached File  Runtime_error.zip ( 72.61K )Number of downloads: 23
 
Go to the top of the page
 
azolder
post Feb 7 2016, 06:06 PM
Post#9



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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.
Go to the top of the page
 
azolder
post Feb 7 2016, 07:10 PM
Post#10



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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. Attached File  Holidays02.zip ( 178.13K )Number of downloads: 30

Summary of Changes:
  • International dates, as defined under the international/regional settings and formats, are now supported
    • Errors generated by inconsistent date formatting were eliminated
    • Hebrew and Hijri dates are formatted consistent with the international/regional format settings
  • Corrected error showing Easter occurring 7 days prior to actual occurrence.
  • Corrected Easter offset calculations, causing errors in dates such as Ash Wednesday
  • Corrected the Gregorian short date computation on frmDates, which propagated through the other date computations.
  • Added year validation inputs for frmHolInput and frmDates.
  • Added/refined notes clarifying Hebrew and Hijri dates on frmHolInput and frmDates.
  • Some changes to the supporting Word document
    • Added Summary of Changes
    • Added GregToHebrewShort function description
    • Minor editing 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:
  • Gregorian Day of Month
  • Gregorian Nth Weekday of Month
  • Easter and Easter Offsets
  • Advent Sundays
  • Hebrew/Jewish Day of Month
  • Hijri/Arabic/Islamic Day of Month

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
  • None - The holiday is not observed, that is there is no 'day off' for it.
  • Weekdays Only - The holiday is observed (a day off) only on weekdays. It is not observed on weekends.
  • Nearest Weekday - The holiday is observed. When it falls on a weekend it is observed on the "nearest workday".
Go to the top of the page
 
LuigiSoft
post Feb 8 2016, 08:58 AM
Post#11



Posts: 75
Joined: 13-May 03
From: NEAR FRANKFURT; GERMANY


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.
Go to the top of the page
 
azolder
post Feb 8 2016, 09:53 AM
Post#12



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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.
Go to the top of the page
 
azolder
post Feb 9 2016, 12:56 PM
Post#13



Posts: 2,428
Joined: 12-February 15
From: SW AZ


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.
Attached File  Holidays021.zip ( 192.38K )Number of downloads: 109

Holiday types supported are:
  • Gregorian Day of Month
  • Gregorian Nth Weekday of Month
  • Easter and Easter Offsets
  • Advent Sundays
  • Hebrew/Jewish Day of Month
  • Hijri/Arabic/Islamic Day of Month

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
  • None - The holiday is not observed, that is there is no 'day off' for it.
  • Weekdays Only - The holiday is observed (a day off) only on weekdays. It is not observed on weekends.
  • Nearest Weekday - The holiday is observed. When it falls on a weekend it is observed on the "nearest workday".

Summary of Changes:
  • Version 2.1
    • Corrected bug in EasterVBA function
    • Corrected minor discrepancy in displaying long date
    • Improved detection of International dates
    • Added modInternational and documentation
  • Version 2
    • International dates, as defined under the international/regional settings and formats, are now supported
    • Errors generated by inconsistent date formatting were eliminated
    • Hebrew and Hijri dates are formatted consistent with the international/regional format settings /Corrected error showing Easter occurring 7 days prior to actual occurrence
    • Corrected Easter offset calculations, causing errors in dates such as Ash Wednesday
    • Corrected the Gregorian short date computation on frmDates, which propagated through the other date computations.
    • Added year validation inputs for frmHolInput and frmDates.
    • Added/refined notes clarifying Hebrew and Hijri dates on frmHolInput and frmDates
.
Go to the top of the page
 
LuigiSoft
post Feb 12 2016, 02:38 PM
Post#14



Posts: 75
Joined: 13-May 03
From: NEAR FRANKFURT; GERMANY


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.
Go to the top of the page
 
azolder
post Feb 12 2016, 04:07 PM
Post#15



Posts: 2,428
Joined: 12-February 15
From: SW AZ


Great!

My pleasure.
cheers.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 12:31 AM