Checking to see if date is holiday

[Craig]

I have a form were I need to check if an estimated date due for shipping is
on a holiday. The first of the year, 4th of July and Christmas is no problem
because they have specified dates. However Memorial Day, Labor Day and
Thanksgiving are not. I've tried some code from Access.Advisor to try and
solve my coding problem, but w/o luck. I need the days returned in a mm/dd
format.

TIA

 

[Al Campagna]

Craig,
I would suggest a table called tblHolidays, where each year you update
what dates (that year) will be holidays.
A field called HolidayDate will hold all the national holidays, as well
as your company's holiday dates.
Do a Dlookup using your DueDate against the tblHolidays...
Use the BeforeUpdate event of DueDate...

If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #" &
DueDate & "#")) Then
MsgBox "This DueDate is a Holiday...", vbOKOnly
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[Klatuu]

Craig,
Al gave you a good answer. But, just to add to it form you, here is a
function that uses a very similar routine. The function will return the
number of working days (Not WeekEnds, Not in Holiday Table).
There is one piece that may be of interest. If you need to look at multiple
dates, you can use the DCount once rather than the DLookup multiple times.

Just a little performance enhancer.

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "holidays", "[holdate] between
#" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Craig]

I have a problem in that I cannot go into my customers application every year
and update the holidays table. So I'm looking for a way to tell me for a
function that will tell me which calendar date Labor Day,Memorial Day and
Thanksgiving may fall on since they are static in the calendar but not for
the specific date that they land on for the given year.

--Craig

Craig,
I would suggest a table called tblHolidays, where each year you update
what dates (that year) will be holidays.
A field called HolidayDate will hold all the national holidays, as well
as your company's holiday dates.
Do a Dlookup using your DueDate against the tblHolidays...
Use the BeforeUpdate event of DueDate...

If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #" &
DueDate & "#")) Then
MsgBox "This DueDate is a Holiday...", vbOKOnly
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

I have a form were I need to check if an estimated date due for shipping is
on a holiday. The first of the year, 4th of July and Christmas is no
problem
because they have specified dates. However Memorial Day, Labor Day and
Thanksgiving are not. I've tried some code from Access.Advisor to try and
solve my coding problem, but w/o luck. I need the days returned in a mm/dd
format.

TIA

 

[Al Campagna]

Craig,
My client updates their table every year themselves. I created a form
for them to allow that, and it's very simple to manitain and use.
See this page I came across... it's for Excel, but the VB looks exactly
the same as Access...
http://www.cpearson.com/excel/holidays.htm

Also, search the web for "holiday" "calculations"... etc for more
holiday calculations.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

I have a problem in that I cannot go into my customers application every
year
and update the holidays table. So I'm looking for a way to tell me for a
function that will tell me which calendar date Labor Day,Memorial Day and
Thanksgiving may fall on since they are static in the calendar but not for
the specific date that they land on for the given year.

--Craig

Craig,
I would suggest a table called tblHolidays, where each year you update
what dates (that year) will be holidays.
A field called HolidayDate will hold all the national holidays, as
well
as your company's holiday dates.
Do a Dlookup using your DueDate against the tblHolidays...
Use the BeforeUpdate event of DueDate...

If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #" &
DueDate & "#")) Then
MsgBox "This DueDate is a Holiday...", vbOKOnly
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

I have a form were I need to check if an estimated date due for shipping
is
on a holiday. The first of the year, 4th of July and Christmas is no
problem
because they have specified dates. However Memorial Day, Labor Day and
Thanksgiving are not. I've tried some code from Access.Advisor to try
and
solve my coding problem, but w/o luck. I need the days returned in a
mm/dd
format.

TIA

 

[Jamie Collins]

Al gave you a good answer.

Tell him your story about the bank who was entrusted with maintaining
their calendar table every year themselves. Is it not the case that
the dates of Memorial Day, Labor Day and Thanksgiving can be predicted
fairly accurately many years in advance?

Jamie.

--

 

[Al Campagna]

Jamie,

the dates of Memorial Day, Labor Day and Thanksgiving can be predicted
fairly accurately many years in advance?

"Fairly accurately?"

I don't know about you Jamie, but... "fairly accurate" just doesn't cut
it
with database applications... or any application for that matter.

 

[Jamie Collins]

"Fairly accurately?"

I don't know about you Jamie, but... "fairly accurate" just doesn't cut
it
with database applications... or any application for that matter.

While I'd *hope* it is indeed the case for you, I think we should
consider some evidence:

http://groups.google.com/group/microsoft.public.access/msg/a7f686d4e84052d4

accessuser1308 asks: "I currently have a field on my form/table that
is set as a number field.
Under the format option I have selected 'currency'. When I enter a
dollar amount it is rounding to the nearest whole dollar. Is there a
way to make it stop doing this, such that it displays the correct
amount?

Al Campagna Microsoft Access MVP replies: "Try Numeric - Single"

Sorry but no, I cannot understand why you would recommend using an
approximate numeric type for monetary data if you truly believe that
"'fairly accurate' just doesn't cut it with database applications."

FWIW I think you may have misinterpreted my caution e.g.

http://en.wikipedia.org/wiki/Memorial_Day
"Memorial Day is a United States federal holiday that is observed on
the last Monday of May"

http://en.wikipedia.org/wiki/Labor_Day
"Labor Day is a United States federal holiday that takes place on the
first Monday in September."

http://en.wikipedia.org/wiki/Thanksgiving
Thanksgiving on the fourth Thursday of November and Canada celebrates
the holiday on the second Monday in October.

IMO a public holiday can only be predicted rather than something to be
relied upon. What if the enterprise in question, government even,
decided to alter the considered date or miss it entirely one year (or
permanently)? None of my applications consider the effects of
political stability e.g. company transfers jurisdiction from US to
Canada.

Jamie.

--

 

[Al Campagna]

Jamie,
I've used Single data type to handle currency for many years, with no
problems. I just control the division and multiplication to two decimal
places.

Regarding the holiday calculation... may I offer a real world problem,
and we can then compare our solutions.

Using a Holiday table....
Client says that a product can be fabricated in 9 (DaysToWeave) days, and
needs to know the fabrication completion date (from some given StartDate).
Holidays and weekend days are to be skipped.
I just add one day to the StartDate (TempDate), and check if TempDate is
a Holiday (according to the table), or if Weekday(TempDate) = 1 or 7, and
loop until WorkDays = 9.

Do Until WorkDays = DaysToWeave
TempDate = Me.StartWeave + Ctr
If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = " &
TempDate))
And WeekDay(TempDate) <> 7 And WeekDay(TempDate) <> 1 Then
WorkDays = WorkDays + 1
Ctr = Ctr + 1
Else
Ctr = Ctr + 1
End If
Me.EndWeave = TempDate
Loop

Would you share your code to handle this problem?
I'm particularly interested in holidays such as Christmas and New Year,
where... if those holidays fall on a Tuesday, Monday would "probably" be
included as a holiday that year, and if it falls on Thursday, Friday would
also be a holiday that year. If those holidays fall in mid week, it's
probably a toss-up as to which day each client might assign.
Also, many clients can have "holidays" that are specifically thier own,
(retooling, inventory, religious, etc..) where there is no production. A
holiday table would make it simple to incorporate those dates.
Also, the issue of "ortability"come into play. The holiday table could
be used in Canada, or England. etc...

Taking into consideration that many OPs in the Access newsgroups are not
experienced Access programmers, I feel my solution is much easier to
understand and implement, and is accurate. My client supervisors have no
problem maintaining a yearly updated Holiday table.
Microsoft also suggests http://support.microsoft.com/kb/210064

By the by, one "very minor" point. If you initiate a post to a "top"
poster, please use a top post, and vice versa. It just makes the flow of
the discussion easier to understand (within that particular thread).
Thanks...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[Jamie Collins]

I've used Single data type to handle currency for many years, with no
problems. I just control the division and multiplication to two decimal
places.

Learning that you have never been burned should not fool anyone into
thinking that playing with fire is safe <g>. Seriously, it may be time
to review your practices. Floating point data has its place (e.g. it's
a good fit for Jet's DATETIME data type because a period of time can
be infinitely divided) but not for discrete data, such as dollar
amounts where the *storage* of inexact values may have legal
implications. Do a google search on "floating point" AND "monetary
data".

But do you not see the contradiction? On the one hand you say "fairly
accurate" is not good enough, on the other you say an approximate data
type is fine if it doesn't present you with any practical problems.

Regarding the holiday calculation... may I offer a real world problem,
and we can then compare our solutions.

Using a Holiday table....
Client says that a product can be fabricated in 9 (DaysToWeave) days, and
needs to know the fabrication completion date (from some given StartDate).
Holidays and weekend days are to be skipped.

My client supervisors have no
problem maintaining a yearly updated Holiday table.

I have a Calendar table with one row for each day over a period,
typically 30 years. I'd then flag the public holidays (could be one
column for each region) based on published criteria ("fourth Thursday
of November" etc) over the same period. Clients are encouraged to
review all holiday dates in a one off exercise. This way, maintenance
is only required in exceptional cases, rather than every year. I've
heard that Julianized business days might be a better approach (harder
for the end user to maintain, though) but note that my Calendar table
has other uses e.g. identifying overlapping periods in history
tables.

I just add one day to the StartDate (TempDate), and check if TempDate is
a Holiday (according to the table), or if Weekday(TempDate) = 1 or 7, and
loop until WorkDays = 9.

"Loop"? I write SQL and let the engine loop to its heart's content
<g>, something like:

WHERE Calendar.is_weekday = 'Y'
AND Calendar.is_holiday = 'N'

as a search condition to remove the non-considered dates.

By the by, one "very minor" point. If you initiate a post to a "top"
poster, please use a top post, and vice versa. It just makes the flow of
the discussion easier to understand (within that particular thread).
Thanks...

I'd never given it any consideration. Now that I have, I politely
decline. Quoting the other guy first means I have the last word

Jamie.

--

 

[Gina Whipp]

Jamie,

Just curious...

How would you handle Jewish Holidays? And not the fact that they start
sundown of the day before, but unlike 'American' holidays they are very hard
to predict what month/day they will be in/on.

 

[Jamie Collins]

How would you handle Jewish Holidays? And not the fact that they start
sundown of the day before, but unlike 'American' holidays they are very hard
to predict what month/day they will be in/on.

My only experience is at the enterprise level i.e. using a Calendar
table to indicate the days that the enterprise consider to be a
holiday, hence should be kept under review by the enterprise. IIRC
Easter cannot be predicated absolutely due to the 'leap seconds' issue
but I'd have high confidence on finding a trusted source of the dates
for the next twenty years, hence would be able to provide the
enterprise with a reasonably stable starting point in terms of public
holidays. Sorry, I have no knowledge of Jewish holidays.

Jamie.

--

 

[Gina Whipp]

I was just curious because I frequently have to deal with them and can
usually find 10 years out but found it much easier to let them 'update' the
table themselves.

Thanks...
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II