Business Days

[Guest]

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

 

[Guest]

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

Klatuu

Just reviewing your response (Finally. been extremely busy)

I need to also report on this. I have a query called Turn Around Times, and
also a report that feeds of this query. the report measures the number of
days between two dates. (Note that the date fields also record the time as
well)

How would I get the report to generate the turn around time less weekends. I
assume that there would be some coding to complete within the query its self
or the report.

Also.... what data does the holiday table need? do you only type in the
dates of the weekends only?

Thanks

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

The only required field is a date field. In the code I sent, the date field
is [Holdate]. I do have a description field, but for this routine it is not
used.
You do not have to put weekends (Saturday or Sunday) in the table. This
routine does not count them as work days. You only need to put in Non work
days that occur Monday through Friday.

Klatuu

Just reviewing your response (Finally. been extremely busy)

I need to also report on this. I have a query called Turn Around Times, and
also a report that feeds of this query. the report measures the number of
days between two dates. (Note that the date fields also record the time as
well)

How would I get the report to generate the turn around time less weekends. I
assume that there would be some coding to complete within the query its self
or the report.

Also.... what data does the holiday table need? do you only type in the
dates of the weekends only?

Thanks

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

Klatuu

I have pasted the code into a standard module. How do I now make my report
generate only calculating business days???

The only required field is a date field. In the code I sent, the date field
is [Holdate]. I do have a description field, but for this routine it is not
used.
You do not have to put weekends (Saturday or Sunday) in the table. This
routine does not count them as work days. You only need to put in Non work
days that occur Monday through Friday.

Klatuu

Just reviewing your response (Finally. been extremely busy)

I need to also report on this. I have a query called Turn Around Times, and
also a report that feeds of this query. the report measures the number of
days between two dates. (Note that the date fields also record the time as
well)

How would I get the report to generate the turn around time less weekends. I
assume that there would be some coding to complete within the query its self
or the report.

Also.... what data does the holiday table need? do you only type in the
dates of the weekends only?

Thanks

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

:

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

In the Text Box on your report where you want to show the number of days,
make the function call the control source. I don't know where you have the
start and end dates to use for the call. If you have text boxes on the
report that show the start and end dates, you can use them. If they are not
on the report, but in the query, and easy way to do this is to create
invisible text boxes somewhere on the report and reference them. For
example, lets say you have 2 text boxes on the report named txtStartDate and
txtEndDate and you have a text box for the number of days named txtDays. Put
this in the control source for txtDays:
=CalcWorkDays(Me.txtStartDate, Me.txtEndDate)

Klatuu

I have pasted the code into a standard module. How do I now make my report
generate only calculating business days???

The only required field is a date field. In the code I sent, the date field
is [Holdate]. I do have a description field, but for this routine it is not
used.
You do not have to put weekends (Saturday or Sunday) in the table. This
routine does not count them as work days. You only need to put in Non work
days that occur Monday through Friday.

Klatuu

Just reviewing your response (Finally. been extremely busy)

I need to also report on this. I have a query called Turn Around Times, and
also a report that feeds of this query. the report measures the number of
days between two dates. (Note that the date fields also record the time as
well)

How would I get the report to generate the turn around time less weekends. I
assume that there would be some coding to complete within the query its self
or the report.

Also.... what data does the holiday table need? do you only type in the
dates of the weekends only?

Thanks

:

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

:

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

Klatuu

I have been poking around the Microsoft website and noticed that I need to
have an add in installed into MS Access. As I am creating this data base for
a large organisation, A lot of the functionality in most of may apps
(including MSAccess) is blocked for security reasons. As I do not have the
appropriate Add In (I cannot remember the add in name) I assume that these
codes will not work (I get #Name in the box that should show the number of
days)

Is there a way of calculating business days without relying on this add in???

In the Text Box on your report where you want to show the number of days,
make the function call the control source. I don't know where you have the
start and end dates to use for the call. If you have text boxes on the
report that show the start and end dates, you can use them. If they are not
on the report, but in the query, and easy way to do this is to create
invisible text boxes somewhere on the report and reference them. For
example, lets say you have 2 text boxes on the report named txtStartDate and
txtEndDate and you have a text box for the number of days named txtDays. Put
this in the control source for txtDays:
=CalcWorkDays(Me.txtStartDate, Me.txtEndDate)

Klatuu

I have pasted the code into a standard module. How do I now make my report
generate only calculating business days???

The only required field is a date field. In the code I sent, the date field
is [Holdate]. I do have a description field, but for this routine it is not
used.
You do not have to put weekends (Saturday or Sunday) in the table. This
routine does not count them as work days. You only need to put in Non work
days that occur Monday through Friday.

:

Klatuu

Just reviewing your response (Finally. been extremely busy)

I need to also report on this. I have a query called Turn Around Times, and
also a report that feeds of this query. the report measures the number of
days between two dates. (Note that the date fields also record the time as
well)

How would I get the report to generate the turn around time less weekends. I
assume that there would be some coding to complete within the query its self
or the report.

Also.... what data does the holiday table need? do you only type in the
dates of the weekends only?

Thanks

:

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

:

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

The code I posted should not require any add ins. I don't know if there is
something else you are doing that will require it. I am running on XP Pro
and Access 2003. I have no add ins. Here is all the References I use:
VBA = C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Access = C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
DAO = C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
Excel = C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
Outlook = C:\Program Files\Microsoft Office\OFFICE11\msoutl.olb

Klatuu

I have been poking around the Microsoft website and noticed that I need to
have an add in installed into MS Access. As I am creating this data base for
a large organisation, A lot of the functionality in most of may apps
(including MSAccess) is blocked for security reasons. As I do not have the
appropriate Add In (I cannot remember the add in name) I assume that these
codes will not work (I get #Name in the box that should show the number of
days)

Is there a way of calculating business days without relying on this add in???

In the Text Box on your report where you want to show the number of days,
make the function call the control source. I don't know where you have the
start and end dates to use for the call. If you have text boxes on the
report that show the start and end dates, you can use them. If they are not
on the report, but in the query, and easy way to do this is to create
invisible text boxes somewhere on the report and reference them. For
example, lets say you have 2 text boxes on the report named txtStartDate and
txtEndDate and you have a text box for the number of days named txtDays. Put
this in the control source for txtDays:
=CalcWorkDays(Me.txtStartDate, Me.txtEndDate)

Klatuu

I have pasted the code into a standard module. How do I now make my report
generate only calculating business days???

:

The only required field is a date field. In the code I sent, the date field
is [Holdate]. I do have a description field, but for this routine it is not
used.
You do not have to put weekends (Saturday or Sunday) in the table. This
routine does not count them as work days. You only need to put in Non work
days that occur Monday through Friday.

:

Klatuu

Just reviewing your response (Finally. been extremely busy)

I need to also report on this. I have a query called Turn Around Times, and
also a report that feeds of this query. the report measures the number of
days between two dates. (Note that the date fields also record the time as
well)

How would I get the report to generate the turn around time less weekends. I
assume that there would be some coding to complete within the query its self
or the report.

Also.... what data does the holiday table need? do you only type in the
dates of the weekends only?

Thanks

:

Sorry to be so long getting back to you. I have been out of town.
The code goes in a standard module.
The Table name is Holidays; however, you can name it whatever you want,
just change the code. I have only 2 fields Holdate and Holdate_Description.

:

Hello Klatuu

Were does this code reside??

Also the table for the holidays. What fields .does it need to contain and
what do I call the table???

:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

Hi Klatuu,

This piece of code is exactly what i'm looking for, but i'm comming up with
#Name? in the TotalDays field.

I have read your entire discussion with Sam earlier this year and believe
i've created everything correct, but no luck.

I have a form with fields Start, End & TotalDays. I have also created a
Table called Holidays with a field HolDate.

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

At present only interested in getting the form working properly, then want
to move onto the report, but i'm sure i can handle that.

Thank you in advance for your assistance.

Greg

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

There is no field named TotalDays. There is a varialbe named intTotalDays.
If you are using TotalDays, that may very well be the problems. Perhaps you
could post back your version of the code so we can see what the issue is.

Hi Klatuu,

This piece of code is exactly what i'm looking for, but i'm comming up with
#Name? in the TotalDays field.

I have read your entire discussion with Sam earlier this year and believe
i've created everything correct, but no luck.

I have a form with fields Start, End & TotalDays. I have also created a
Table called Holidays with a field HolDate.

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

At present only interested in getting the form working properly, then want
to move onto the report, but i'm sure i can handle that.

Thank you in advance for your assistance.

Greg

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[Guest]

I am trying to run this module from a query but i get the following message:
Undifined Function, 'CalWorkDays' in expression

Can anyone help?

There is no field named TotalDays. There is a varialbe named intTotalDays.
If you are using TotalDays, that may very well be the problems. Perhaps you
could post back your version of the code so we can see what the issue is.

Hi Klatuu,

This piece of code is exactly what i'm looking for, but i'm comming up with
#Name? in the TotalDays field.

I have read your entire discussion with Sam earlier this year and believe
i've created everything correct, but no luck.

I have a form with fields Start, End & TotalDays. I have also created a
Table called Holidays with a field HolDate.

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

At present only interested in getting the form working properly, then want
to move onto the report, but i'm sure i can handle that.

Thank you in advance for your assistance.

Greg

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

You will need to build a Holiday table to know about not working days during
the period.

:

Does anyone know how to calculate the number of business days or working days
between date1 and date2?

 

[John Vinson]

I am trying to run this module from a query but i get the following message:
Undifined Function, 'CalWorkDays' in expression

Can anyone help?

Sounds like a simple typo: the function definition downthread reads

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

Try using CalcWorkDays instead of CalWorkDays in your query.

John W. Vinson[MVP]

 

[Guest]

Thanks for your reply, however that is not the mistake. I wrote down the
incorrect system message. It sais the following:
Undifined Function, 'CalcWorkDays' in expression

Can you walk me through the process. I am trying to have this function count
the number of days between to date fields named BKDISR and BKCLOS in a query.
The query has other fields in it. I need the correct syntax to have this
function work within this query.

Thanks for your help

 

[John Vinson]

Thanks for your reply, however that is not the mistake. I wrote down the
incorrect system message. It sais the following:
Undifined Function, 'CalcWorkDays' in expression

Can you walk me through the process. I am trying to have this function count
the number of days between to date fields named BKDISR and BKCLOS in a query.
The query has other fields in it. I need the correct syntax to have this
function work within this query.

Several steps:

- Copy and paste the CalcWorkDays() function into a new (or existing)
module on the Modules tab.
- Select Debug... Compile <your database name> from the menu. Fix any
compilation errors.
- Save the Module with some name OTHER THAN CalcWorkDays - modules and
procedures share the same "namespace" so you can't have the same name
for both.
- In the Query grid for your query, type

WorkDaysElapsed: CalcWorkDays(... <enter appropriate parameters> ...)

into a vacant Field cell.

If this doesn't work, please repost the entire code in your module and
the SQL view of your query.

John W. Vinson[MVP]

 

[Guest]

John,

Don't know what I am doing wrong I saved the module with another name and
copiled it but it won't let me past First Day. I get the following message:
Compile error: Argument not optional.

The code I used is the one in this thread, I will paste it below.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

 

[Douglas J. Steele]

Is "First Day" on a line by itself (it's hard to tell with the
word-wrapping)

It's supposed to be a continuation of the comment on the previous line. The
whole line is supposed to be:

' Add one to include First Day

You'll need to check the other comments as well. Without comments, your code
should be:

Function CalcWorkDays( _
dtmStart As Date, _
dtmEnd As Date _
) As Integer

Dim intTotalDays As Integer
Dim dtmToday As Date
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1

dtmToday = dtmStart
Do Until dtmToday = dtmEnd
If Weekday(dtmToday, vbMonday) 5 Then
intTotalDays = intTotalDays - 1
ElseIf Not IsNull(DLookup("[Holdate]", _
"Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then
intTotalDays = intTotalDays - 1
End If
dtmToday = DateAdd("d", 1, dtmToday)
Loop
CalcWorkDays = intTotalDays
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John,

Don't know what I am doing wrong I saved the module with another name and
copiled it but it won't let me past First Day. I get the following
message:
Compile error: Argument not optional.

The code I used is the one in this thread, I will paste it below.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total
days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare
date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

Several steps:

- Copy and paste the CalcWorkDays() function into a new (or existing)
module on the Modules tab.
- Select Debug... Compile <your database name> from the menu. Fix any
compilation errors.
- Save the Module with some name OTHER THAN CalcWorkDays - modules and
procedures share the same "namespace" so you can't have the same name
for both.
- In the Query grid for your query, type

WorkDaysElapsed: CalcWorkDays(... <enter appropriate parameters> ...)

into a vacant Field cell.

If this doesn't work, please repost the entire code in your module and
the SQL view of your query.

John W. Vinson[MVP]

 

[Guest]

Thanks Douglas,

That code worked and I understood what you meant about First day being part
of the comment. However, I am still getting my first message:
Undifined Function, 'CalcWorkDays' in expression

Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];

and I used the code you gave me for the module.

Again thanks for the help.

Is "First Day" on a line by itself (it's hard to tell with the
word-wrapping)

It's supposed to be a continuation of the comment on the previous line. The
whole line is supposed to be:

' Add one to include First Day

You'll need to check the other comments as well. Without comments, your code
should be:

Function CalcWorkDays( _
dtmStart As Date, _
dtmEnd As Date _
) As Integer

Dim intTotalDays As Integer
Dim dtmToday As Date
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1

dtmToday = dtmStart
Do Until dtmToday = dtmEnd
If Weekday(dtmToday, vbMonday) 5 Then
intTotalDays = intTotalDays - 1
ElseIf Not IsNull(DLookup("[Holdate]", _
"Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then
intTotalDays = intTotalDays - 1
End If
dtmToday = DateAdd("d", 1, dtmToday)
Loop
CalcWorkDays = intTotalDays
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John,

Don't know what I am doing wrong I saved the module with another name and
copiled it but it won't let me past First Day. I get the following
message:
Compile error: Argument not optional.

The code I used is the one in this thread, I will paste it below.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total
days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare
date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function

On Fri, 23 Dec 2005 08:43:02 -0800, "Moses"

Thanks for your reply, however that is not the mistake. I wrote down the
incorrect system message. It sais the following:
Undifined Function, 'CalcWorkDays' in expression

Can you walk me through the process. I am trying to have this function
count
the number of days between to date fields named BKDISR and BKCLOS in a
query.
The query has other fields in it. I need the correct syntax to have this
function work within this query.

Several steps:

- Copy and paste the CalcWorkDays() function into a new (or existing)
module on the Modules tab.
- Select Debug... Compile <your database name> from the menu. Fix any
compilation errors.
- Save the Module with some name OTHER THAN CalcWorkDays - modules and
procedures share the same "namespace" so you can't have the same name
for both.
- In the Query grid for your query, type

WorkDaysElapsed: CalcWorkDays(... <enter appropriate parameters> ...)

into a vacant Field cell.

If this doesn't work, please repost the entire code in your module and
the SQL view of your query.

John W. Vinson[MVP]

 

[John Vinson]

Thanks Douglas,

That code worked and I understood what you meant about First day being part
of the comment. However, I am still getting my first message:
Undifined Function, 'CalcWorkDays' in expression

Here is the SQL code behind my query:
SELECT MTGLIBP1_CHMSTR01.[ACCT#], MTGLIBP1_CHMSTR01.INTPTD,
MTGLIBP1_CHBKRPCY.BKDISR, MTGLIBP1_CHBKRPCY.BKCLOS
FROM MTGLIBP1_CHBKRPCY INNER JOIN MTGLIBP1_CHMSTR01 ON
MTGLIBP1_CHBKRPCY.[ACCT#] = MTGLIBP1_CHMSTR01.[ACCT#];

and I used the code you gave me for the module.

Earlier you said you had

In the TotalDays field in control source I have
=CalcWorkDays(Me.txtStart,Me.txtEnd)

and that you were getting an error "argument not optional". This could
happen if either txtStart or txtEnd were NULL.

If you're still getting the Undefined Function error, then perhaps
either you have a misspelling somewhere, or the function is defined as
Private Function rather than Public Function in your Module.

John W. Vinson[MVP]