Coding dates difference in VBA for Access 2003

[J. LeRoy]

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[KARL DEWEY]

This method uses 3 queries. Create a table named Holidays with field named
Holiday (you might want a text field naming the holiday). Create a table
named CountNumber with field named CountNUM containing numbers from 0 (zero)
through you maximum spread.
Dates-Business_Days --
SELECT Dates_LeRoy.ID, Dates_LeRoy.Start, Dates_LeRoy.End,
DateAdd("d",[CountNUM],[Start]) AS [Days Between]
FROM CountNumber, Dates_LeRoy
WHERE (((DateAdd("d",[CountNUM],[Start]))<=[End]))
ORDER BY Dates_LeRoy.ID, DateAdd("d",[CountNUM],[Start]);

Dates-Business_Days_1 --
SELECT [Dates-Business_Days].ID, [Dates-Business_Days].Start,
[Dates-Business_Days].End, [Dates-Business_Days].[Days Between]
FROM [Dates-Business_Days] LEFT JOIN Holidays ON [Dates-Business_Days].[Days
Between] = Holidays.Holiday
WHERE (((Holidays.Holiday) Is Null))
ORDER BY [Dates-Business_Days].ID, [Dates-Business_Days].[Days Between];

Dates-Business_Days_2
SELECT [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End, Count([Dates-Business_Days_1].[Days Between]) AS
[CountOfDays Between]
FROM [Dates-Business_Days_1]
GROUP BY [Dates-Business_Days_1].ID, [Dates-Business_Days_1].Start,
[Dates-Business_Days_1].End;

 

[Klatuu]

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[J. LeRoy]

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[Klatuu]

Post the code where you are calling the function so I can have a look.
--
Dave Hargis, Microsoft Access MVP

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[J. LeRoy]

Thanks, here is the code:
Function CalcWorkDays(dtmStartDate As Date, dtmEndDate As Date)

On Error GoTo CalcWorkDays_Error

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

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

Post the code where you are calling the function so I can have a look.
--
Dave Hargis, Microsoft Access MVP

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP


:

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[Klatuu]

Actually, what I need is the code where you are calling the function.
--
Dave Hargis, Microsoft Access MVP

Thanks, here is the code:
Function CalcWorkDays(dtmStartDate As Date, dtmEndDate As Date)

On Error GoTo CalcWorkDays_Error

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

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

Post the code where you are calling the function so I can have a look.
--
Dave Hargis, Microsoft Access MVP

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

:

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP


:

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[J. LeRoy]

Sorry here it's

Private Sub cmdCalcDays_Click()
On Error GoTo Err_cmdCalcDays_Click

Dim stAppName As String

stAppName = "TimeTracking subform"
Call Shell(stAppName, 1)

Exit_cmdCalcDays_Click:
Exit Sub

Err_cmdCalcDays_Click:
MsgBox Err.Description
Resume Exit_cmdCalcDays_Click

End Sub

Actually, what I need is the code where you are calling the function.
--
Dave Hargis, Microsoft Access MVP

Thanks, here is the code:
Function CalcWorkDays(dtmStartDate As Date, dtmEndDate As Date)

On Error GoTo CalcWorkDays_Error

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

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

Post the code where you are calling the function so I can have a look.
--
Dave Hargis, Microsoft Access MVP


:

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

:

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP


:

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[Klatuu]

That is incorrect. The Shell statement is used to start external
applications, not open a form in your current database.

Maybe you could explain a bit more about what you are doing and where you
want to do this calculation.
--
Dave Hargis, Microsoft Access MVP

Sorry here it's

Private Sub cmdCalcDays_Click()
On Error GoTo Err_cmdCalcDays_Click

Dim stAppName As String

stAppName = "TimeTracking subform"
Call Shell(stAppName, 1)

Exit_cmdCalcDays_Click:
Exit Sub

Err_cmdCalcDays_Click:
MsgBox Err.Description
Resume Exit_cmdCalcDays_Click

End Sub

Actually, what I need is the code where you are calling the function.
--
Dave Hargis, Microsoft Access MVP

Thanks, here is the code:
Function CalcWorkDays(dtmStartDate As Date, dtmEndDate As Date)

On Error GoTo CalcWorkDays_Error

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

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

:

Post the code where you are calling the function so I can have a look.
--
Dave Hargis, Microsoft Access MVP


:

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

:

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP


:

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just

 

[raskew via AccessMonster.com]

Hi -

See DateDiffw() at http://support.microsoft.com/kb/288194

Best Wishes - Bob

 

[J. LeRoy]

Klatuu,
Thank you so much, I finaly figure out: I just needed to create another form
and add the command button to do the calculation. Everything is working very
fine.
Again thank you for you assistant,
J. LeRoy

That is incorrect. The Shell statement is used to start external
applications, not open a form in your current database.

Maybe you could explain a bit more about what you are doing and where you
want to do this calculation.
--
Dave Hargis, Microsoft Access MVP

Sorry here it's

Private Sub cmdCalcDays_Click()
On Error GoTo Err_cmdCalcDays_Click

Dim stAppName As String

stAppName = "TimeTracking subform"
Call Shell(stAppName, 1)

Exit_cmdCalcDays_Click:
Exit Sub

Err_cmdCalcDays_Click:
MsgBox Err.Description
Resume Exit_cmdCalcDays_Click

End Sub

Actually, what I need is the code where you are calling the function.
--
Dave Hargis, Microsoft Access MVP


:

Thanks, here is the code:
Function CalcWorkDays(dtmStartDate As Date, dtmEndDate As Date)

On Error GoTo CalcWorkDays_Error

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

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

:

Post the code where you are calling the function so I can have a look.
--
Dave Hargis, Microsoft Access MVP


:

Dear Klatuu,
Thank you so much, I wrote the code but I do not get any value; I may not
doing it right. I created three tables: tblEmployee, tblHolidays and
"tblDailywork" that has StartDate and EndDate fields I'm trying to get the
result in the Number of Days field, therefore I set the code to be called
from the "frmDailywork" I'm not getting anything, and then I selected the
Number of Days's textbox and set to Before_Update in Event property I still
can't get a value. What can I do? Please can you assist me?

Again Thank you,

:

Here is a function that does exactly that. Put it in a standard module and
you can call it from anywhere in your application. You will need a holiday
table with one record for each holiday. In this code the table name is
holidays and the field in holdate.

'---------------------------------------------------------------------------------------
' 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

--
Dave Hargis, Microsoft Access MVP


:

I'm a New Access Developer learning to create a Tracking Data Application
with MS Access with VBA. I'm stock with my project because I can't see any
result in my coding. Would you please help to write a VBA code into Ms
Access 2003 to calculate difference days between days minus Saturday, Sunday,
And 10 days Holidays?

Best Regards,
Just