Criteria for Auto Populating

[Guest]

I am trying to auto populate a resolved date based on whether an option from
a combo box was picked or whether a check box was clicked. If the Compliment
check box is checked then I need the resolved date to auto populate for 30
working days from today's date. If an option from the Complaint combo box is
chosen then I need to have the resolve date populate 30 business days from
today's date. Since I am new with access I'm not sure where to start - I
have copied the workday's module, I'm not sure if this is what I need, but I
am not quite sure what to do with it now that I have is saved as a module.

 

[Jeff Boyce]

I'm not sure I understand the difference between "30 working days" and "30
business days"...

Rather than store a calculated value (?your "resolved date") in a table,
consider simply calculating it (via a query). Storing calculated values can
cause problems with synchronization. They also take up space, usually
unnecesarily, and it's slower to marshall data into/out of table storage
than to simply do the calculation in a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

My question would then be - Do I link the form to a query? Because the form
is linked to a table how would I go about doing that?

Well the module that I copied has a couple different scenarios in it and it
was called the workdays module so that is just what I saved it as.

 

[Jeff Boyce]

Actually, a common development technique is to NEVER build a form directly
on a table. Instead, create a query against the table, perhaps with
selection and/or sorting criteria, then base the form on the query.

The form has a source ... the source can be a table or a query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I have tried to do this with this particular form. I have tried to change
the properties so that it pulls from a query instead of a form and when I go
the view option nothing is there. Also another question about that, if I am
currently using the form to input data into a table, and I have a query in
the back ground that appends that data to a different table and deletes it
out of the table that it is currently in, I'm I doing something wrong by
doing that as well?

 

[Jeff Boyce]

See responses in-line below...

I have tried to do this with this particular form. I have tried to change
the properties so that it pulls from a query instead of a form and when I
go
the view option nothing is there.

If the form is set as a data-entry form (a form property), you wouldn't.

Also another question about that, if I am
currently using the form to input data into a table, and I have a query in
the back ground

I don't know what you mean by "in the background"

that appends that data to a different table

So you are adding the data into one table, then using a query to add it to
another?

and deletes it
out of the table that it is currently in

Wait, out of the table that the form puts it into, or out of some third
table?

, I'm I doing something wrong by
doing that as well?

I really don't have a very clear picture of what you are doing. Worse, I
don't have any idea what you want to accomplish.

You've described "how" you are trying to do something (i.e., using a form,
using a query, deleting from a table, ...). If you describe "what" you want
to accomplish (imagine explaining to a 90 year old grandmother who doesn't
know a thing about your subject area OR about computers), the newsgroup
readers might be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

What I want to accomplish is to have date auto populate a field based on
criteria from another field. The criteria would depend on if a check box is
checked or if an items in a drop down box has been chosen.

Example
If I checked the box that the communication that we received was a compiment
then I would want the resolve date to populate with a date of 30 business
days in the future.

Then I have a drop down box to choose reasons for the complaint that was
received. If a choice is chosen from that drop down box then I would want
the resolve date to auto populate with 7 days in the future.

I hope this helps.

 

[Jeff Boyce]

It sounds like you are trying to store a "calculated" value in the table,
based on another field. If you are already storing the value of the
checkbox and/or the value of a combo box, it is frequently not necessary to
store a calculated future date.

Since the calculation would be based on other values in the table, you can
use a query to "calculate" your "resolve date".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

My next question would be - How do I do the calculation to include only the
business days. I have the following module:

But I am not quite sure where it goes or how it works.

Public Function IsWeekend(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a weekend
'In: dteDate is the date to be checked
'Out: Returns either True if the date is a Saturday or Sunday
and False if any other
' day of the week
'Example: IsWeekend(#2/19/03#) returns False
'****************************************

Select Case Weekday(dtmDate)
Case vbSaturday, vbSunday: IsWeekend = True
Case Else: IsWeekend = False
End Select

End Function

Public Function IsHoliday(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a Holiday
' NOTE: This function requires a table called tblHolidays
with one field called
' HolDate which is a date/time field and includes all the
dates you consider
' holidays
'In: dteDate is the date to be checked
'Out: Returns either True if the date is found in the table (a
holiday) and False
' if the date is not found in the table
'Example: IsHoliday(#1/1/03#) returns True (New Year's Day)
'****************************************

Dim db As Database
Dim rs As Recordset
Dim strCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)
strCriteria = "[HolDate] = #" & dtmDate & "#"
rs.FindFirst strCriteria
If rs.NoMatch Then
IsHoliday = False
Else
IsHoliday = True
End If

Set rs = Nothing
Set db = Nothing

End Function

Public Function FirstDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the first calendar day of month
'Example: FirstDayOfMonth(#1/4/03#) returns 1/1/03
'****************************************

FirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Public Function LastDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the last calendar day of month
'Example: LastDayOfMonth(#1/4/03#) returns 1/31/03
'****************************************

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(dtmDate),
Month(dtmDate) + 1, 1))

End Function

Public Function FirstWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first business (working) day of the month
'In: dteDate is the date to be checked
'Out: Returns the first business day of the month
'Example: FirstWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnFirstWorkday As Boolean

dtmTemp = FirstDayOfMonth(dtmDate)
blnFirstWorkday = False
Do Until blnFirstWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnFirstWorkday = True
End If
Loop
FirstWorkDayOfMonth = dtmTemp

End Function

Public Function LastWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last business (working) day of the month
'In: dteDate is the date to be checked
'Out: Returns the last business day of the month
'Example: LastWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnLastWorkday As Boolean

dtmTemp = LastDayOfMonth(dtmDate)
blnLastWorkday = False
Do Until blnLastWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnLastWorkday = True
End If
Loop
LastWorkDayOfMonth = dtmTemp

End Function

Public Function DaysInMonth(dtmDate As Date) As Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of calendar days in month
'In: dteDate is the date to be checked
'Out: Returns number of calendar day in month
'Example: DaysInMonth(#1/4/03#) returns 31
'****************************************

Dim dtmTemp As Date

dtmTemp = LastDayOfMonth(dtmDate)
DaysInMonth = CInt(Format(dtmTemp, "dd"))

End Function

Public Function NextWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the next business (working) day
'In: dteDate is the date to be checked
'Out: Returns the next business day
'Example: NextWorkDay(#12/31/02#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnNextWorkday As Boolean

dtmTemp = dtmDate + 1
blnNextWorkday = False
Do Until blnNextWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnNextWorkday = True
End If
Loop
NextWorkDay = dtmTemp

End Function

Public Function PreviousWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the previous business (working) day
'In: dteDate is the date to be checked
'Out: Returns the previous business day
'Example: PreviousWorkDay(#1/2/03#) returns 12/31/02
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnPreviousWorkday As Boolean

dtmTemp = dtmDate - 1
blnPreviousWorkday = False
Do Until blnPreviousWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnPreviousWorkday = True
End If
Loop
PreviousWorkDay = dtmTemp

End Function

Public Function CountHolidays(dtmStartDate As Date, dtmEndDate As Date) As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of holidays between two dates
'In: dteStartDate is the first date, dtmEndDate is the last
date
'Out: Returns the number of holidays between start and end date
'Example: CountHolidays(#12/31/02#, #1/3/03#) returns 1
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intHolidayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp
End If

intHolidayCount = 0
intDaysBetweenDates = dtmEndDate - dtmStartDate
For i = 0 To intDaysBetweenDates
If IsHoliday(DateAdd("d", i, dtmStartDate)) = True Then
intHolidayCount = intHolidayCount + 1
Next i
CountHolidays = intHolidayCount

End Function

Public Function CountWorkDays(dtmStartDate As Date, dtmEndDate As Date) As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of business days between two dates
'In: dteStartDate is the first date, dtmEndDate is the last
date
'Out: Returns the number of business between start and end date
'Example: CountWorkDays(#12/31/02#, #1/3/03#) returns 3
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intWorkDayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp
End If

intWorkDayCount = 0
intDaysBetweenDates = dtmEndDate - dtmStartDate
For i = 0 To intDaysBetweenDates
If IsHoliday(DateAdd("d", i, dtmStartDate)) = False And _
IsWeekend(DateAdd("d", i, dtmStartDate)) = False Then
intWorkDayCount = intWorkDayCount + 1
Next i
CountWorkDays = intWorkDayCount

End Function

Public Function AddWorkDays(dtmDate As Date, intDays As Integer) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Adds a number of business days to a starting date
'In: dteDate is the date, intDays is the number of days to add
'Out: Returns the date the number of business days after the
start date
'Example: AddWorkDays(#12/31/02#, 4) returns 1/6/03
' (1/1/01 is a holiday (New Year's Day), 1/4/03 and 1/5/03
are weekend days)
'****************************************

Dim dtmTemp As Date
Dim i As Integer

dtmTemp = dtmDate
For i = 1 To intDays
dtmTemp = NextWorkDay(dtmTemp)
Next i
AddWorkDays = dtmTemp

End Function

' ################ End Code ########################

 

[Jeff Boyce]

I believe you can find a function that calculates business days by checking
at mvps.org or using Google.com.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My next question would be - How do I do the calculation to include only
the
business days. I have the following module:

But I am not quite sure where it goes or how it works.

Public Function IsWeekend(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a weekend
'In: dteDate is the date to be checked
'Out: Returns either True if the date is a Saturday or
Sunday
and False if any other
' day of the week
'Example: IsWeekend(#2/19/03#) returns False
'****************************************

Select Case Weekday(dtmDate)
Case vbSaturday, vbSunday: IsWeekend = True
Case Else: IsWeekend = False
End Select

End Function

Public Function IsHoliday(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a Holiday
' NOTE: This function requires a table called
tblHolidays
with one field called
' HolDate which is a date/time field and includes all
the
dates you consider
' holidays
'In: dteDate is the date to be checked
'Out: Returns either True if the date is found in the table
(a
holiday) and False
' if the date is not found in the table
'Example: IsHoliday(#1/1/03#) returns True (New Year's Day)
'****************************************

Dim db As Database
Dim rs As Recordset
Dim strCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)
strCriteria = "[HolDate] = #" & dtmDate & "#"
rs.FindFirst strCriteria
If rs.NoMatch Then
IsHoliday = False
Else
IsHoliday = True
End If

Set rs = Nothing
Set db = Nothing

End Function

Public Function FirstDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the first calendar day of month
'Example: FirstDayOfMonth(#1/4/03#) returns 1/1/03
'****************************************

FirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Public Function LastDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the last calendar day of month
'Example: LastDayOfMonth(#1/4/03#) returns 1/31/03
'****************************************

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(dtmDate),
Month(dtmDate) + 1, 1))

End Function

Public Function FirstWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first business (working) day of the
month
'In: dteDate is the date to be checked
'Out: Returns the first business day of the month
'Example: FirstWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnFirstWorkday As Boolean

dtmTemp = FirstDayOfMonth(dtmDate)
blnFirstWorkday = False
Do Until blnFirstWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnFirstWorkday = True
End If
Loop
FirstWorkDayOfMonth = dtmTemp

End Function

Public Function LastWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last business (working) day of the month
'In: dteDate is the date to be checked
'Out: Returns the last business day of the month
'Example: LastWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnLastWorkday As Boolean

dtmTemp = LastDayOfMonth(dtmDate)
blnLastWorkday = False
Do Until blnLastWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnLastWorkday = True
End If
Loop
LastWorkDayOfMonth = dtmTemp

End Function

Public Function DaysInMonth(dtmDate As Date) As Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of calendar days in month
'In: dteDate is the date to be checked
'Out: Returns number of calendar day in month
'Example: DaysInMonth(#1/4/03#) returns 31
'****************************************

Dim dtmTemp As Date

dtmTemp = LastDayOfMonth(dtmDate)
DaysInMonth = CInt(Format(dtmTemp, "dd"))

End Function

Public Function NextWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the next business (working) day
'In: dteDate is the date to be checked
'Out: Returns the next business day
'Example: NextWorkDay(#12/31/02#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnNextWorkday As Boolean

dtmTemp = dtmDate + 1
blnNextWorkday = False
Do Until blnNextWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnNextWorkday = True
End If
Loop
NextWorkDay = dtmTemp

End Function

Public Function PreviousWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the previous business (working) day
'In: dteDate is the date to be checked
'Out: Returns the previous business day
'Example: PreviousWorkDay(#1/2/03#) returns 12/31/02
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnPreviousWorkday As Boolean

dtmTemp = dtmDate - 1
blnPreviousWorkday = False
Do Until blnPreviousWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnPreviousWorkday = True
End If
Loop
PreviousWorkDay = dtmTemp

End Function

Public Function CountHolidays(dtmStartDate As Date, dtmEndDate As Date) As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of holidays between two dates
'In: dteStartDate is the first date, dtmEndDate is the last
date
'Out: Returns the number of holidays between start and end
date
'Example: CountHolidays(#12/31/02#, #1/3/03#) returns 1
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intHolidayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp
End If

intHolidayCount = 0
intDaysBetweenDates = dtmEndDate - dtmStartDate
For i = 0 To intDaysBetweenDates
If IsHoliday(DateAdd("d", i, dtmStartDate)) = True Then
intHolidayCount = intHolidayCount + 1
Next i
CountHolidays = intHolidayCount

End Function

Public Function CountWorkDays(dtmStartDate As Date, dtmEndDate As Date) As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of business days between two
dates
'In: dteStartDate is the first date, dtmEndDate is the last
date
'Out: Returns the number of business between start and end
date
'Example: CountWorkDays(#12/31/02#, #1/3/03#) returns 3
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intWorkDayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp
End If

intWorkDayCount = 0
intDaysBetweenDates = dtmEndDate - dtmStartDate
For i = 0 To intDaysBetweenDates
If IsHoliday(DateAdd("d", i, dtmStartDate)) = False And _
IsWeekend(DateAdd("d", i, dtmStartDate)) = False Then
intWorkDayCount = intWorkDayCount + 1
Next i
CountWorkDays = intWorkDayCount

End Function

Public Function AddWorkDays(dtmDate As Date, intDays As Integer) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Adds a number of business days to a starting date
'In: dteDate is the date, intDays is the number of days to
add
'Out: Returns the date the number of business days after the
start date
'Example: AddWorkDays(#12/31/02#, 4) returns 1/6/03
' (1/1/01 is a holiday (New Year's Day), 1/4/03 and
1/5/03
are weekend days)
'****************************************

Dim dtmTemp As Date
Dim i As Integer

dtmTemp = dtmDate
For i = 1 To intDays
dtmTemp = NextWorkDay(dtmTemp)
Next i
AddWorkDays = dtmTemp

End Function

' ################ End Code ########################

It sounds like you are trying to store a "calculated" value in the table,
based on another field. If you are already storing the value of the
checkbox and/or the value of a combo box, it is frequently not necessary
to
store a calculated future date.

Since the calculation would be based on other values in the table, you
can
use a query to "calculate" your "resolve date".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I did the search and found some useful information, the only problem is, I
don't know what to do with the information that I have. I have the module
but because I'm not sure about code, I'm not sure what to do with the module
now that I have it. I guess it will continue to be a mystery to me. Thank
you for the help on the other issue.

I believe you can find a function that calculates business days by checking
at mvps.org or using Google.com.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My next question would be - How do I do the calculation to include only
the
business days. I have the following module:

But I am not quite sure where it goes or how it works.

Public Function IsWeekend(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a weekend
'In: dteDate is the date to be checked
'Out: Returns either True if the date is a Saturday or
Sunday
and False if any other
' day of the week
'Example: IsWeekend(#2/19/03#) returns False
'****************************************

Select Case Weekday(dtmDate)
Case vbSaturday, vbSunday: IsWeekend = True
Case Else: IsWeekend = False
End Select

End Function

Public Function IsHoliday(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a Holiday
' NOTE: This function requires a table called
tblHolidays
with one field called
' HolDate which is a date/time field and includes all
the
dates you consider
' holidays
'In: dteDate is the date to be checked
'Out: Returns either True if the date is found in the table
(a
holiday) and False
' if the date is not found in the table
'Example: IsHoliday(#1/1/03#) returns True (New Year's Day)
'****************************************

Dim db As Database
Dim rs As Recordset
Dim strCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)
strCriteria = "[HolDate] = #" & dtmDate & "#"
rs.FindFirst strCriteria
If rs.NoMatch Then
IsHoliday = False
Else
IsHoliday = True
End If

Set rs = Nothing
Set db = Nothing

End Function

Public Function FirstDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the first calendar day of month
'Example: FirstDayOfMonth(#1/4/03#) returns 1/1/03
'****************************************

FirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Public Function LastDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the last calendar day of month
'Example: LastDayOfMonth(#1/4/03#) returns 1/31/03
'****************************************

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(dtmDate),
Month(dtmDate) + 1, 1))

End Function

Public Function FirstWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first business (working) day of the
month
'In: dteDate is the date to be checked
'Out: Returns the first business day of the month
'Example: FirstWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnFirstWorkday As Boolean

dtmTemp = FirstDayOfMonth(dtmDate)
blnFirstWorkday = False
Do Until blnFirstWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnFirstWorkday = True
End If
Loop
FirstWorkDayOfMonth = dtmTemp

End Function

Public Function LastWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last business (working) day of the month
'In: dteDate is the date to be checked
'Out: Returns the last business day of the month
'Example: LastWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnLastWorkday As Boolean

dtmTemp = LastDayOfMonth(dtmDate)
blnLastWorkday = False
Do Until blnLastWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnLastWorkday = True
End If
Loop
LastWorkDayOfMonth = dtmTemp

End Function

Public Function DaysInMonth(dtmDate As Date) As Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of calendar days in month
'In: dteDate is the date to be checked
'Out: Returns number of calendar day in month
'Example: DaysInMonth(#1/4/03#) returns 31
'****************************************

Dim dtmTemp As Date

dtmTemp = LastDayOfMonth(dtmDate)
DaysInMonth = CInt(Format(dtmTemp, "dd"))

End Function

Public Function NextWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the next business (working) day
'In: dteDate is the date to be checked
'Out: Returns the next business day
'Example: NextWorkDay(#12/31/02#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnNextWorkday As Boolean

dtmTemp = dtmDate + 1
blnNextWorkday = False
Do Until blnNextWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnNextWorkday = True
End If
Loop
NextWorkDay = dtmTemp

End Function

Public Function PreviousWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the previous business (working) day
'In: dteDate is the date to be checked
'Out: Returns the previous business day
'Example: PreviousWorkDay(#1/2/03#) returns 12/31/02
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnPreviousWorkday As Boolean

dtmTemp = dtmDate - 1
blnPreviousWorkday = False
Do Until blnPreviousWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnPreviousWorkday = True
End If
Loop
PreviousWorkDay = dtmTemp

End Function

Public Function CountHolidays(dtmStartDate As Date, dtmEndDate As Date) As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of holidays between two dates
'In: dteStartDate is the first date, dtmEndDate is the last
date
'Out: Returns the number of holidays between start and end
date
'Example: CountHolidays(#12/31/02#, #1/3/03#) returns 1
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intHolidayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp
End If

intHolidayCount = 0
intDaysBetweenDates = dtmEndDate - dtmStartDate
For i = 0 To intDaysBetweenDates
If IsHoliday(DateAdd("d", i, dtmStartDate)) = True Then
intHolidayCount = intHolidayCount + 1
Next i
CountHolidays = intHolidayCount

End Function

Public Function CountWorkDays(dtmStartDate As Date, dtmEndDate As Date) As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of business days between two
dates
'In: dteStartDate is the first date, dtmEndDate is the last
date
'Out: Returns the number of business between start and end
date
'Example: CountWorkDays(#12/31/02#, #1/3/03#) returns 3
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intWorkDayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp

 

[Jeff Boyce]

If you create a new query, "feed" it the table with your dates, and include
the function that calculates business days (using the dates from the
included table), you should get the function's output.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I did the search and found some useful information, the only problem is, I
don't know what to do with the information that I have. I have the module
but because I'm not sure about code, I'm not sure what to do with the
module
now that I have it. I guess it will continue to be a mystery to me.
Thank
you for the help on the other issue.

I believe you can find a function that calculates business days by
checking
at mvps.org or using Google.com.

Regards

Jeff Boyce
Microsoft Office/Access MVP

My next question would be - How do I do the calculation to include only
the
business days. I have the following module:

But I am not quite sure where it goes or how it works.

Public Function IsWeekend(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a weekend
'In: dteDate is the date to be checked
'Out: Returns either True if the date is a Saturday or
Sunday
and False if any other
' day of the week
'Example: IsWeekend(#2/19/03#) returns False
'****************************************

Select Case Weekday(dtmDate)
Case vbSaturday, vbSunday: IsWeekend = True
Case Else: IsWeekend = False
End Select

End Function

Public Function IsHoliday(dtmDate As Date) As Boolean

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine if the date provided is a Holiday
' NOTE: This function requires a table called
tblHolidays
with one field called
' HolDate which is a date/time field and includes all
the
dates you consider
' holidays
'In: dteDate is the date to be checked
'Out: Returns either True if the date is found in the
table
(a
holiday) and False
' if the date is not found in the table
'Example: IsHoliday(#1/1/03#) returns True (New Year's Day)
'****************************************

Dim db As Database
Dim rs As Recordset
Dim strCriteria As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblHolidays", dbOpenSnapshot)
strCriteria = "[HolDate] = #" & dtmDate & "#"
rs.FindFirst strCriteria
If rs.NoMatch Then
IsHoliday = False
Else
IsHoliday = True
End If

Set rs = Nothing
Set db = Nothing

End Function

Public Function FirstDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the first calendar day of month
'Example: FirstDayOfMonth(#1/4/03#) returns 1/1/03
'****************************************

FirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Public Function LastDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last calendar day of month
'In: dteDate is the date to be checked
'Out: Returns the last calendar day of month
'Example: LastDayOfMonth(#1/4/03#) returns 1/31/03
'****************************************

LastDayOfMonth = DateAdd("d", -1, DateSerial(Year(dtmDate),
Month(dtmDate) + 1, 1))

End Function

Public Function FirstWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the first business (working) day of the
month
'In: dteDate is the date to be checked
'Out: Returns the first business day of the month
'Example: FirstWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnFirstWorkday As Boolean

dtmTemp = FirstDayOfMonth(dtmDate)
blnFirstWorkday = False
Do Until blnFirstWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnFirstWorkday = True
End If
Loop
FirstWorkDayOfMonth = dtmTemp

End Function

Public Function LastWorkDayOfMonth(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the last business (working) day of the
month
'In: dteDate is the date to be checked
'Out: Returns the last business day of the month
'Example: LastWorkDayOfMonth(#1/4/03#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnLastWorkday As Boolean

dtmTemp = LastDayOfMonth(dtmDate)
blnLastWorkday = False
Do Until blnLastWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnLastWorkday = True
End If
Loop
LastWorkDayOfMonth = dtmTemp

End Function

Public Function DaysInMonth(dtmDate As Date) As Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of calendar days in month
'In: dteDate is the date to be checked
'Out: Returns number of calendar day in month
'Example: DaysInMonth(#1/4/03#) returns 31
'****************************************

Dim dtmTemp As Date

dtmTemp = LastDayOfMonth(dtmDate)
DaysInMonth = CInt(Format(dtmTemp, "dd"))

End Function

Public Function NextWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the next business (working) day
'In: dteDate is the date to be checked
'Out: Returns the next business day
'Example: NextWorkDay(#12/31/02#) returns 1/2/03
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnNextWorkday As Boolean

dtmTemp = dtmDate + 1
blnNextWorkday = False
Do Until blnNextWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", 1, dtmTemp)
Else
blnNextWorkday = True
End If
Loop
NextWorkDay = dtmTemp

End Function

Public Function PreviousWorkDay(dtmDate As Date) As Date

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the previous business (working) day
'In: dteDate is the date to be checked
'Out: Returns the previous business day
'Example: PreviousWorkDay(#1/2/03#) returns 12/31/02
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim dtmTemp As Date
Dim blnPreviousWorkday As Boolean

dtmTemp = dtmDate - 1
blnPreviousWorkday = False
Do Until blnPreviousWorkday = True
If IsWeekend(dtmTemp) = True Or IsHoliday(dtmTemp) = True Then
dtmTemp = DateAdd("d", -1, dtmTemp)
Else
blnPreviousWorkday = True
End If
Loop
PreviousWorkDay = dtmTemp

End Function

Public Function CountHolidays(dtmStartDate As Date, dtmEndDate As Date)
As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of holidays between two dates
'In: dteStartDate is the first date, dtmEndDate is the
last
date
'Out: Returns the number of holidays between start and
end
date
'Example: CountHolidays(#12/31/02#, #1/3/03#) returns 1
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intHolidayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp
End If

intHolidayCount = 0
intDaysBetweenDates = dtmEndDate - dtmStartDate
For i = 0 To intDaysBetweenDates
If IsHoliday(DateAdd("d", i, dtmStartDate)) = True Then
intHolidayCount = intHolidayCount + 1
Next i
CountHolidays = intHolidayCount

End Function

Public Function CountWorkDays(dtmStartDate As Date, dtmEndDate As Date)
As
Integer

'****************************************
'Created By: Robert L. Johnson III
'Mod Date: February 19, 2003
'Purpose: Determine the number of business days between two
dates
'In: dteStartDate is the first date, dtmEndDate is the
last
date
'Out: Returns the number of business between start and
end
date
'Example: CountWorkDays(#12/31/02#, #1/3/03#) returns 3
' (1/1/01 is a holiday (New Year's Day))
'****************************************

Dim intWorkDayCount As Integer
Dim intDaysBetweenDates As Integer
Dim i As Integer
Dim dtmTemp As Date

If dtmStartDate > dtmEndDate Then
dtmTemp = dtmStartDate
dtmStartDate = dtmEndDate
dtmEndDate = dtmTemp