skip holiday

[klp via AccessMonster.com]

I have a Customer Order entry screen that has a due date field on it.
Currently it calls a module that I created to default to the next day, skip
holidays' and weekends'. Which works great. This field has the option to use
a calendar to select the date. However, I do not have access to this calendar
since this is an application that we purchased(we have access to all tables,
queries, forms, reports, macros, modules) excluding the calendar. What I
would like is if someone happens to select say April 6th for the due date
(which is Good Friday) an message would come up stating that it's a holiday
and to choose another date. Is this possible without using the calendar? Here
is my logic that the field calls.

****************
Public Function NextWorkDay(dtmSomeDay As Date)
Dim dtmDayOff As Date
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Loop
NextWorkDay = dtmSomeDay
End Function
*****************
Public Function IsWorkDay(dtmSomeDay As Date) As Boolean
Dim blnWorkingDay
blnWorkingDay = Weekday(dtmSomeDay, vbMonday) < 6
If blnWorkingDay Then
blnWorkingDay = IsNull(DLookup("[HolidayDate]", "tblHolidays", _
"[HolidayDate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function
******************

Any help is well appreciated

Kim P

 

[Hansford cornett]

Dim DateCk as date
DateCk = dtmSomeDay (if dtmSomDay is the field you first input.)

at top of code like right under dtmSomeDay = DateAdd

If dtmSomeDay = HolidayDate then
MsgBox "This date is a Holiday Date, Please try another"
else
the above code you submitted

End if
I believe this will work for you....

 

[klp via AccessMonster.com]

Great, thank you. However, it gives me an error when I process the code that
it cannot find the field HolidayDate. So do I need to open the recordset here
or do some sort of lookup to find the field? In the 2nd public function I
created it is found through the DLookup function but in the 1st public
function it is not defined.

Any help appreciated.

Kim P.

Dim DateCk as date
DateCk = dtmSomeDay (if dtmSomDay is the field you first input.)

at top of code like right under dtmSomeDay = DateAdd

If dtmSomeDay = HolidayDate then
MsgBox "This date is a Holiday Date, Please try another"
else
the above code you submitted

End if
I believe this will work for you....

I have a Customer Order entry screen that has a due date field on it.
Currently it calls a module that I created to default to the next day, skip

[quoted text clipped - 31 lines]

Kim P

 

[Hansford cornett]

YES you will have to look it up and them compair it as you have. I think
that should work. You may also set Holiday Date as a Public variable so you
do not have to keep looking it up you can just assign to it once it is set as
a public or global variable depending on the version of access your running.

GOOD LUCK....

 

[klp via AccessMonster.com]

Okay, I declared the HolidayDate as a public varibable to make it easier.
It's not working. I'm not sure if I placed it in the wrong place or not.
Could you look at it and verify if it's correct or incorrect? This is what I
have.

Public Function NextWorkDay(dtmSomeDay As Date)
Dim DateCk As Date

dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
If dtmSomeDay = HolidayDate Then
MsgBox "The date you selected is a holiday date. Please re-
select a date."
Else
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
End If
Loop
NextWorkDay = dtmSomeDay

End Function

Thank you and you have been a great help.

YES you will have to look it up and them compair it as you have. I think
that should work. You may also set Holiday Date as a Public variable so you
do not have to keep looking it up you can just assign to it once it is set as
a public or global variable depending on the version of access your running.

GOOD LUCK....

I have a Customer Order entry screen that has a due date field on it.
Currently it calls a module that I created to default to the next day, skip

[quoted text clipped - 31 lines]

Kim P

 

[Hansford cornett]

Sorry I was not clear

depending on what verson of Access your working on

establish a module.

In the module set the following

Global HolidayDate as String

or

Public HolidayDate as String



Then I think your alright because your code is assigning a date to the
HolidayDate


This should work if not I am sorry I guess I am just not smart enough to get
this right and repost your original question stating you have a Public
Variable that you are trying to set to hold the HOLIDAYDATE in one of your
functions.

Hope this works.....



--
Hansford D. Cornett

Okay, I declared the HolidayDate as a public varibable to make it easier.
It's not working. I'm not sure if I placed it in the wrong place or not.
Could you look at it and verify if it's correct or incorrect? This is what I
have.

Public Function NextWorkDay(dtmSomeDay As Date)
Dim DateCk As Date

dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
Do Until IsWorkDay(dtmSomeDay)
If dtmSomeDay = HolidayDate Then
MsgBox "The date you selected is a holiday date. Please re-
select a date."
Else
dtmSomeDay = DateAdd("d", 1, dtmSomeDay)
End If
Loop
NextWorkDay = dtmSomeDay

End Function

Thank you and you have been a great help.

YES you will have to look it up and them compair it as you have. I think
that should work. You may also set Holiday Date as a Public variable so you
do not have to keep looking it up you can just assign to it once it is set as
a public or global variable depending on the version of access your running.

GOOD LUCK....

I have a Customer Order entry screen that has a due date field on it.
Currently it calls a module that I created to default to the next day, skip

[quoted text clipped - 31 lines]

Kim P

 

[klp via AccessMonster.com]

Sorry for keep asking questions but I want to be sure. Can I declare
HolidayDate in the module I already have for workday code? Or do I need to
create a separate one? I have access 2003 btw.

Once again thank you.

Sorry I was not clear

depending on what verson of Access your working on

establish a module.

In the module set the following

Global HolidayDate as String

or

Public HolidayDate as String

Then I think your alright because your code is assigning a date to the
HolidayDate

This should work if not I am sorry I guess I am just not smart enough to get
this right and repost your original question stating you have a Public
Variable that you are trying to set to hold the HOLIDAYDATE in one of your
functions.

Hope this works.....

Okay, I declared the HolidayDate as a public varibable to make it easier.
It's not working. I'm not sure if I placed it in the wrong place or not.

[quoted text clipped - 31 lines]

 

[Hansford cornett]

Any module should work. You can also place a unbound variable on your form
named HolidayDate and see what ever is being assigned to it when it is
assigned. just create the variable and NAME IT.

You questions are fine. I am glad I can help.

Dwight
--
Hansford D. Cornett

Sorry for keep asking questions but I want to be sure. Can I declare
HolidayDate in the module I already have for workday code? Or do I need to
create a separate one? I have access 2003 btw.

Once again thank you.

Sorry I was not clear

depending on what verson of Access your working on

establish a module.

In the module set the following

Global HolidayDate as String

or

Public HolidayDate as String

Then I think your alright because your code is assigning a date to the
HolidayDate

This should work if not I am sorry I guess I am just not smart enough to get
this right and repost your original question stating you have a Public
Variable that you are trying to set to hold the HOLIDAYDATE in one of your
functions.

Hope this works.....

Okay, I declared the HolidayDate as a public varibable to make it easier.
It's not working. I'm not sure if I placed it in the wrong place or not.

[quoted text clipped - 31 lines]

Kim P