weekday function

[klp via AccessMonster.com]

I have a customer orders form where there is a due date. Currently we can
either select the date from the calendar OR put in your own date. The default
for the due date field is set to = Date() + 1. For next day shipping.
However, I want to be able to skip the weekends and only acknowledge the week
days. So I created a function as follows:

Function WorkDays(dtCurrDate As Date, dtNewDate As Date) As Integer

If Weekday(dtCurrDate, vbUseSystemDayOfWeek) = 1 Then
'if Sunday
dtNewDate = dtCurrDate + 1
ElseIf Weekday(dtCurrDate, vbUseSystemDayOfWeek) = 7 Then
'if Saturday
dtNewDate = dtCurrDate + 2
End If

End Function

Not having used the modules that much, I'm not sure where you place the logic
in the customer orders form, and how to call it. I have an idea but am
stumped somewhat. Would I place my logic on my due date field or on the form
myself?

Thanks in advance.

 

[JethroUK©]

replace

= Date() + 1

your function does look a little overweight for what you need

aircode

Function DueDate()
Duedate = date() +1
if weekday (duedate) = 7 then duedate = duedate + 2 ' Sat i think
if weekday (duedate) = 1 then duedate = duedate + 1 'Sun i think
end function

 

[JethroUK©]

should read:

Function DueDate() as date

replace

= Date() + 1

your function does look a little overweight for what you need

aircode

Function DueDate()
Duedate = date() +1
if weekday (duedate) = 7 then duedate = duedate + 2 ' Sat i think
if weekday (duedate) = 1 then duedate = duedate + 1 'Sun i think
end function

 

[klp via AccessMonster.com]

Okay just so I have an understanding - I've created the function in a module.
So then this code will be read all the time on that form(assuming that's what
aircode means). I don't have to do anything to my customer orders form?

should read:

Function DueDate() as date

replace

[quoted text clipped - 38 lines]

 

[Klatuu]

There is one thing missing in your logic. What is tomorrow is a holiday?
What if it is Friday, and Monday is a holiday?
So, here is a routine that will find the next work day excluding Saturdays,
Sundays, and any dates in a Holiday table. I have a table cleverly named
Holidays that has two fields. A date/time field named HolDate and a text
field named HolDate_Desc. The first being the date of a holiday and the
latter being the description, for example
HolDate - 05/2005/2006
HolDate_Desc - International Klatuu Day

If you choose to add this functionality to your system, you can create your
own table using whatever naming convention you use and change the code to
suit. Now, where to put the code and how to execute it. Put the functions
in a standard module. Do Not name the module the same name as either of the
functions. To call it, put it in the Default Value property of your Due Date
Control

=NextWorkDay(Date)

Okay just so I have an understanding - I've created the function in a module.
So then this code will be read all the time on that form(assuming that's what
aircode means). I don't have to do anything to my customer orders form?

should read:

Function DueDate() as date

replace

[quoted text clipped - 38 lines]

Thanks in advance.

 

[Arvin Meyer [MVP]]

Which is precisely what this function is built to handle:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

There is one thing missing in your logic. What is tomorrow is a holiday?
What if it is Friday, and Monday is a holiday?
So, here is a routine that will find the next work day excluding
Saturdays,
Sundays, and any dates in a Holiday table. I have a table cleverly named
Holidays that has two fields. A date/time field named HolDate and a text
field named HolDate_Desc. The first being the date of a holiday and the
latter being the description, for example
HolDate - 05/2005/2006
HolDate_Desc - International Klatuu Day

If you choose to add this functionality to your system, you can create
your
own table using whatever naming convention you use and change the code to
suit. Now, where to put the code and how to execute it. Put the
functions
in a standard module. Do Not name the module the same name as either of
the
functions. To call it, put it in the Default Value property of your Due
Date
Control

=NextWorkDay(Date)

Okay just so I have an understanding - I've created the function in a
module.
So then this code will be read all the time on that form(assuming that's
what
aircode means). I don't have to do anything to my customer orders form?

should read:

Function DueDate() as date

replace

[quoted text clipped - 38 lines]

Thanks in advance.

 

[Klatuu]

That's good.
The reason mine is split in two functions is that either can be called.
IsWorkDay lets you know if a date is a work day or not. NextWorkDay just
uses IsWorkDay in cases where you want to know the next business day.

Which is precisely what this function is built to handle:

http://www.datastrat.com/Code/GetBusinessDay.txt
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

There is one thing missing in your logic. What is tomorrow is a holiday?
What if it is Friday, and Monday is a holiday?
So, here is a routine that will find the next work day excluding
Saturdays,
Sundays, and any dates in a Holiday table. I have a table cleverly named
Holidays that has two fields. A date/time field named HolDate and a text
field named HolDate_Desc. The first being the date of a holiday and the
latter being the description, for example
HolDate - 05/2005/2006
HolDate_Desc - International Klatuu Day

If you choose to add this functionality to your system, you can create
your
own table using whatever naming convention you use and change the code to
suit. Now, where to put the code and how to execute it. Put the
functions
in a standard module. Do Not name the module the same name as either of
the
functions. To call it, put it in the Default Value property of your Due
Date
Control

=NextWorkDay(Date)

Okay just so I have an understanding - I've created the function in a
module.
So then this code will be read all the time on that form(assuming that's
what
aircode means). I don't have to do anything to my customer orders form?

JethroUK© wrote:
should read:

Function DueDate() as date

replace

[quoted text clipped - 38 lines]

Thanks in advance.

 

[klp via AccessMonster.com]

Great, thank you. The logic makes perfect sense and I already had a table for
Holidays. Now, like I said before I haven't had the opportunity to work w/
modules much. I inserted logic into my module utility. So now that I've done
that. I have to call it correct? Do I do that on my customers form or on the
Due Date. In one of the replies I was told to put it in the default of the
Due Date field. Just need a shed of light on this part.

Which is precisely what this function is built to handle:

http://www.datastrat.com/Code/GetBusinessDay.txt

There is one thing missing in your logic. What is tomorrow is a holiday?
What if it is Friday, and Monday is a holiday?

[quoted text clipped - 35 lines]

 

[Klatuu]

The Default Value of the due date would be correct. Just replace the Date()
+ 1 with a call to your function:
=NextWorkDay(Date)

Great, thank you. The logic makes perfect sense and I already had a table for
Holidays. Now, like I said before I haven't had the opportunity to work w/
modules much. I inserted logic into my module utility. So now that I've done
that. I have to call it correct? Do I do that on my customers form or on the
Due Date. In one of the replies I was told to put it in the default of the
Due Date field. Just need a shed of light on this part.

Which is precisely what this function is built to handle:

http://www.datastrat.com/Code/GetBusinessDay.txt

There is one thing missing in your logic. What is tomorrow is a holiday?
What if it is Friday, and Monday is a holiday?

[quoted text clipped - 35 lines]

Thanks in advance.

 

[klp via AccessMonster.com]

Okay I see, but now when I do that I get an error w/ the wrong number of
arguments. Because in my Function it's defined as Function NextWorkDay
(dtStart As Date, intDay As Integer). I can declare the variables by using
the dim statement that works okay. But it will then give me the same error.
So I tried doing it this way:

Function NextWorkDay(stStart As Date)

Dim intDay As Integer

In my form I set the Due Date default as this: = NextWorkDay(Date) when I
entered it read
=NextWorkDay("Date"). Giving me #Error in the due date field when the form is
executed. What in the world am I doing wrong here that I can't get this thing
to work?

The Default Value of the due date would be correct. Just replace the Date()
+ 1 with a call to your function:
=NextWorkDay(Date)

Great, thank you. The logic makes perfect sense and I already had a table for
Holidays. Now, like I said before I haven't had the opportunity to work w/

[quoted text clipped - 11 lines]

 

[Klatuu]

There are a few possibilities.
First, I don't know what code you are using for NextWorkDay(), mine or the
one posted by Arvin Meyer.
If you are using mine, you only need the date.
Also, I did not see where you posted the error you are getting or what line
you are getting the error on.
It is also necessary the functions are in a standard module or in the form's
module. I suggest a standard module because that way, it can be called from
other forms, queries, or reports. Be aware a module can't contain any
functions or subs that have the same name as the module.
Review the above, then post back with the error number and the code where it
is occuring.

Okay I see, but now when I do that I get an error w/ the wrong number of
arguments. Because in my Function it's defined as Function NextWorkDay
(dtStart As Date, intDay As Integer). I can declare the variables by using
the dim statement that works okay. But it will then give me the same error.
So I tried doing it this way:

Function NextWorkDay(stStart As Date)

Dim intDay As Integer

In my form I set the Due Date default as this: = NextWorkDay(Date) when I
entered it read
=NextWorkDay("Date"). Giving me #Error in the due date field when the form is
executed. What in the world am I doing wrong here that I can't get this thing
to work?

The Default Value of the due date would be correct. Just replace the Date()
+ 1 with a call to your function:
=NextWorkDay(Date)

Great, thank you. The logic makes perfect sense and I already had a table for
Holidays. Now, like I said before I haven't had the opportunity to work w/

[quoted text clipped - 11 lines]

Thanks in advance.

 

[klp via AccessMonster.com]

Alright - I'm not sure but when reviewing all of the postings on this subject,
the one you posted on 6/29 explains clearly. But one thing is missing the
logic. I was using Arvin's because I didn't see where you had posted the
routine, just an example. And the 1st postings are by someone other than you.
Am I missing something here. So to answer your question I was using Arvin's.
Now if I could see what you have then I would use that. I'm getting error 91.
Object variable or with block variable not set. I click okay but I have to
keep clicking and the message doesn't go away. I can't see where or what line
it's hitting because it won't let me do anything. I placed the logic is a
modlue called modUtility w/ only the logic for business days. Sorry for all
this confusion.

There are a few possibilities.
First, I don't know what code you are using for NextWorkDay(), mine or the
one posted by Arvin Meyer.
If you are using mine, you only need the date.
Also, I did not see where you posted the error you are getting or what line
you are getting the error on.
It is also necessary the functions are in a standard module or in the form's
module. I suggest a standard module because that way, it can be called from
other forms, queries, or reports. Be aware a module can't contain any
functions or subs that have the same name as the module.
Review the above, then post back with the error number and the code where it
is occuring.

Okay I see, but now when I do that I get an error w/ the wrong number of
arguments. Because in my Function it's defined as Function NextWorkDay

[quoted text clipped - 21 lines]

 

[Klatuu]

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("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

Alright - I'm not sure but when reviewing all of the postings on this subject,
the one you posted on 6/29 explains clearly. But one thing is missing the
logic. I was using Arvin's because I didn't see where you had posted the
routine, just an example. And the 1st postings are by someone other than you.
Am I missing something here. So to answer your question I was using Arvin's.
Now if I could see what you have then I would use that. I'm getting error 91.
Object variable or with block variable not set. I click okay but I have to
keep clicking and the message doesn't go away. I can't see where or what line
it's hitting because it won't let me do anything. I placed the logic is a
modlue called modUtility w/ only the logic for business days. Sorry for all
this confusion.

There are a few possibilities.
First, I don't know what code you are using for NextWorkDay(), mine or the
one posted by Arvin Meyer.
If you are using mine, you only need the date.
Also, I did not see where you posted the error you are getting or what line
you are getting the error on.
It is also necessary the functions are in a standard module or in the form's
module. I suggest a standard module because that way, it can be called from
other forms, queries, or reports. Be aware a module can't contain any
functions or subs that have the same name as the module.
Review the above, then post back with the error number and the code where it
is occuring.

Okay I see, but now when I do that I get an error w/ the wrong number of
arguments. Because in my Function it's defined as Function NextWorkDay

[quoted text clipped - 21 lines]

Thanks in advance.

 

[klp via AccessMonster.com]

I started over. I created a module called modBusinessDays. Plunked in the
logic you gave me. I went to my customer form and in my DueDate field
properties, I replaced =Date() + 1 with =NextWorkDay(Date) in the default
value field. After I enter that in and hit tab, I does this = NextWorkDay
("Date"). So then when I view my form, it gives me an #Error. I can still
enter information in when adding a new sales order but when I open my form I
wanted to see the next work day, so it should've said 7/7/06. Any other
suggestions.

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("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

Alright - I'm not sure but when reviewing all of the postings on this subject,
the one you posted on 6/29 explains clearly. But one thing is missing the

[quoted text clipped - 26 lines]

 

[klp via AccessMonster.com]

Disregard my last post. I wasn't paying attention, but I didn't put the ()
after Date(ie. NextWorkDay(Date()). It worked when I did that. So I'll see
tomorrow if it will default to 7/10. Thank you for all your time, help and
paitence. It was greatly appreciated.

I started over. I created a module called modBusinessDays. Plunked in the
logic you gave me. I went to my customer form and in my DueDate field
properties, I replaced =Date() + 1 with =NextWorkDay(Date) in the default
value field. After I enter that in and hit tab, I does this = NextWorkDay
("Date"). So then when I view my form, it gives me an #Error. I can still
enter information in when adding a new sales order but when I open my form I
wanted to see the next work day, so it should've said 7/7/06. Any other
suggestions.

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

[quoted text clipped - 20 lines]

 

[Klatuu]

In the Default Value property of your control:
NextWorkDate(Date())

No = sign
Put parenthesis behind Date so Access will know it is the Date function and
not a string value.

I started over. I created a module called modBusinessDays. Plunked in the
logic you gave me. I went to my customer form and in my DueDate field
properties, I replaced =Date() + 1 with =NextWorkDay(Date) in the default
value field. After I enter that in and hit tab, I does this = NextWorkDay
("Date"). So then when I view my form, it gives me an #Error. I can still
enter information in when adding a new sales order but when I open my form I
wanted to see the next work day, so it should've said 7/7/06. Any other
suggestions.

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("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmSomeDay & "#"))
End If
IsWorkDay = blnWorkingDay
End Function

Alright - I'm not sure but when reviewing all of the postings on this subject,
the one you posted on 6/29 explains clearly. But one thing is missing the

[quoted text clipped - 26 lines]

Thanks in advance.