Business/Holiday dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am still learning and would like the help of the group. i have a from that
has a date field. i will like to restrict the input dates to only business or
non holiday dates (no weekends). restriction should include a message that
the date entered is invalid. thanks for your help
 
Gregory,
You'll need a table of Holidays for National holidays, and "other"
holidays your user might encounter. (ex. Friday after Thanksgiving)
Try this code to make the decision... (all one line)
and, referring to a user input date as TempDate...

If IsNull(DLookup("[HolidayDate]", "tblHolidays",
"HolidayDate = #" & TempDate & "#")) And
WeekDay(TempDate) <> 7
And WeekDay(TempDate) <> 1 Then
' this is not a Holiday or a weekend day
Else
' it is Holiday or weekend
End if
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
 
you'll need to create a table of holiday dates, as

tblHolidays
holDate (date/time field, primary key)
<note that i did not call the field simply "Date", as that is a reserved
word in Access and should never be used to name anything that you create.>
you can add an optional Text field to the table to describe the holiday if
you want to.

then open the data entry form in Design view, click on the date control, and
add code to the control's BeforeUpdate event procedure, as

If DatePart("w", Me!DateControlName, vbMonday) > 5 Then
Cancel = True
MsgBox "this is a weekend date"
ElseIf DCount(1, "tblHolidays", "holDate = #" _
& Me!DateControlName & "#") > 0 Then
Cancel = True
MsgBox "this is a holiday date"
End If

replace DateControlName with the correct name of the date control in the
form, of course.

hth
 
You can also enforce it by means of a referential operation. This would mean
that invalid dates would be prohibited however they are entered, so control
is not exercised solely at form level, making the application more
bullet-proof. First you'd create a WorkdaysCalendar table, which is simply a
table of all non-holiday weekdays over whatever period of time is
appropriate. This table can be created by calling the function below as
follows, which you can do from the debug window (Press Ctrl+G to open it).
Call the function like so to make a calendar from this year for 10 years
ahead for instance:

MakeCalendar "WorkdaysCalendar", #01/01/2007#, #12/31/2017#, 2,3,4,5,6

This will make a calendar of all weekdays over the period, so you then need
to delete the rows for holidays. This can be done manually or via a 'delete'
query like so:

PARAMETERS [Enter holiday date:] DATETIME;
DELETE *
FROM WorkdaysCalendar
WHERE calDate = [Enter holiday date:];

Running this query will prompt for a date and then delete the date entered
at the prompt from the table. A more flexible approach would be to run a
query like this via a 'Holidays' dialogue form in which one or more dates can
be selected in a multiselect list box and then all deleted form the table at
a single button click.

Create a relationship between this table and the existing table on the date
columns and enforce referential integrity. This will prevent any date not in
WorkdaysCalendar being entered into the existing table. In your form you can
control it at control level by putting this in the BeforeUpdate event
procedure of the date control:

Const conMESSAGE = "The date entered is a weekend or holiday date."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "calDate = #" & Format(ctrl,"mm/dd/yyyy") & "#"

If IsNull(DLookup("calDate", "WorkdaysCalendar", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Date"
Cancel = True
End If

You might well find the table created is useful for other purposes, e.g. for
counting all working days between two dates, which you can do with the DCount
function or in a query, to count the workdays per project in a Projects table
for instance with ProjectStart and ProjectEnd date columns:

SELECT Project, COUNT(*) AS WorkdaysPerProject
FROM Projects INNER JOIN WordaysCalendar
ON (WorkdaysCalendar.caldate BETWEEN
Projects.ProjectStart AND Projects.ProjectEnd)
GROUP BY Project;

You can of course use the same function to create any other auxiliary
calendar tables which you might need.

Here's the code to create the calendar table. Paste it into any standard
module in the database and call it as described above:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Ken Sheridan
Stafford, England
 
Nice code, but may I suggest, as have the others here, that creating and
maintaining a holiday table is much easier and contains only 8 to 10 records
instead of 250 or more per year.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ken Sheridan said:
You can also enforce it by means of a referential operation. This would
mean
that invalid dates would be prohibited however they are entered, so
control
is not exercised solely at form level, making the application more
bullet-proof. First you'd create a WorkdaysCalendar table, which is
simply a
table of all non-holiday weekdays over whatever period of time is
appropriate. This table can be created by calling the function below as
follows, which you can do from the debug window (Press Ctrl+G to open it).
Call the function like so to make a calendar from this year for 10 years
ahead for instance:

MakeCalendar "WorkdaysCalendar", #01/01/2007#, #12/31/2017#, 2,3,4,5,6

This will make a calendar of all weekdays over the period, so you then
need
to delete the rows for holidays. This can be done manually or via a
'delete'
query like so:

PARAMETERS [Enter holiday date:] DATETIME;
DELETE *
FROM WorkdaysCalendar
WHERE calDate = [Enter holiday date:];

Running this query will prompt for a date and then delete the date entered
at the prompt from the table. A more flexible approach would be to run a
query like this via a 'Holidays' dialogue form in which one or more dates
can
be selected in a multiselect list box and then all deleted form the table
at
a single button click.

Create a relationship between this table and the existing table on the
date
columns and enforce referential integrity. This will prevent any date not
in
WorkdaysCalendar being entered into the existing table. In your form you
can
control it at control level by putting this in the BeforeUpdate event
procedure of the date control:

Const conMESSAGE = "The date entered is a weekend or holiday date."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "calDate = #" & Format(ctrl,"mm/dd/yyyy") & "#"

If IsNull(DLookup("calDate", "WorkdaysCalendar", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Date"
Cancel = True
End If

You might well find the table created is useful for other purposes, e.g.
for
counting all working days between two dates, which you can do with the
DCount
function or in a query, to count the workdays per project in a Projects
table
for instance with ProjectStart and ProjectEnd date columns:

SELECT Project, COUNT(*) AS WorkdaysPerProject
FROM Projects INNER JOIN WordaysCalendar
ON (WorkdaysCalendar.caldate BETWEEN
Projects.ProjectStart AND Projects.ProjectEnd)
GROUP BY Project;

You can of course use the same function to create any other auxiliary
calendar tables which you might need.

Here's the code to create the calendar table. Paste it into any standard
module in the database and call it as described above:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Ken Sheridan
Stafford, England

Gregory said:
i am still learning and would like the help of the group. i have a from
that
has a date field. i will like to restrict the input dates to only
business or
non holiday dates (no weekends). restriction should include a message
that
the date entered is invalid. thanks for your help
Click to expand...
Click to expand...
 
I disagree. Enforcement of a constraint via the logical model is always to
be preferred to application dependency. Not only does it allow for
portability, but also (and this is important in a distributed application)
for user maintenance whereas enforcement at form level would require
amendment of the form definition in the event of the 'weekend' being changed.
The latter is not an unrealistic scenario; some companies here have moved to
a four-day working week of longer days to reduce overheads, and provide
employees with a longer weekends. With a calendar table all that's required
if a company stops working Fridays for instance, is to execute an SQL
statement to delete all rows where the weekday is a Friday after the date
when the change is to be implemented.

As for the size of the 'calendar' table, this is not a significant
consideration; less than 3,000 rows over a ten year period is chickenfeed.

Incidentally an alternative method for creating auxiliary calendar tables is
suggested by Celko; he recommends serially filling a column in a spreadsheet
application and importing into the database application.

One area where an auxiliary calendar table really comes into play is in
relation to applications such as room reservations (hotels, business meetings
etc). The following query for instance will find all vacant rooms within a
date range defined by the parameters (which in a real world application would
be references to controls on a dialogue form of course).

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DATETIME;
SELECT Rooms.RoomNumber, C1.CalDate
FROM Rooms, Calendar AS C1
WHERE C1.CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND NOT EXISTS
(SELECT *
FROM RoomOccupations, Calendar AS C2
WHERE C2.CalDate >= EntryDate
AND (C2.CalDate <= DepartureDate OR DepartureDate IS NULL)
AND C2.CalDate = C1.CalDate
AND RoomOccupations.RoomNumber = Rooms.RoomNumber);

I also once used this for a dental appointment reservations application
where the 'calendar' contained one row per 'minimum time slot' rather than
per day, the 'room' being a dentist, dental hygienist etc. That required
further extension off course as it was necessary to take account of each
staff member's personal leave days as well as the Sunday (the surgery worked
Saturday mornings) and public holidays. This can be done simply enough via
the logical model, however.

Ken Sheridan
Stafford, England

Arvin Meyer said:
Nice code, but may I suggest, as have the others here, that creating and
maintaining a holiday table is much easier and contains only 8 to 10 records
instead of 250 or more per year.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ken Sheridan said:
You can also enforce it by means of a referential operation. This would
mean
that invalid dates would be prohibited however they are entered, so
control
is not exercised solely at form level, making the application more
bullet-proof. First you'd create a WorkdaysCalendar table, which is
simply a
table of all non-holiday weekdays over whatever period of time is
appropriate. This table can be created by calling the function below as
follows, which you can do from the debug window (Press Ctrl+G to open it).
Call the function like so to make a calendar from this year for 10 years
ahead for instance:

MakeCalendar "WorkdaysCalendar", #01/01/2007#, #12/31/2017#, 2,3,4,5,6

This will make a calendar of all weekdays over the period, so you then
need
to delete the rows for holidays. This can be done manually or via a
'delete'
query like so:

PARAMETERS [Enter holiday date:] DATETIME;
DELETE *
FROM WorkdaysCalendar
WHERE calDate = [Enter holiday date:];

Running this query will prompt for a date and then delete the date entered
at the prompt from the table. A more flexible approach would be to run a
query like this via a 'Holidays' dialogue form in which one or more dates
can
be selected in a multiselect list box and then all deleted form the table
at
a single button click.

Create a relationship between this table and the existing table on the
date
columns and enforce referential integrity. This will prevent any date not
in
WorkdaysCalendar being entered into the existing table. In your form you
can
control it at control level by putting this in the BeforeUpdate event
procedure of the date control:

Const conMESSAGE = "The date entered is a weekend or holiday date."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "calDate = #" & Format(ctrl,"mm/dd/yyyy") & "#"

If IsNull(DLookup("calDate", "WorkdaysCalendar", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Date"
Cancel = True
End If

You might well find the table created is useful for other purposes, e.g.
for
counting all working days between two dates, which you can do with the
DCount
function or in a query, to count the workdays per project in a Projects
table
for instance with ProjectStart and ProjectEnd date columns:

SELECT Project, COUNT(*) AS WorkdaysPerProject
FROM Projects INNER JOIN WordaysCalendar
ON (WorkdaysCalendar.caldate BETWEEN
Projects.ProjectStart AND Projects.ProjectEnd)
GROUP BY Project;

You can of course use the same function to create any other auxiliary
calendar tables which you might need.

Here's the code to create the calendar table. Paste it into any standard
module in the database and call it as described above:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Ken Sheridan
Stafford, England

Gregory said:
i am still learning and would like the help of the group. i have a from
that
has a date field. i will like to restrict the input dates to only
business or
non holiday dates (no weekends). restriction should include a message
that
the date entered is invalid. thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
Of course, in your scenario, deleting all the rows would be a mistake since
any existing record that depended on a Friday would no longer be accurate.
So your SQL statement needs to be altered, which also makes it application
dependent. The code I proposed would also need to be altered. The point is
that in either case if there is a change, intervention is required. Yearly
intervention is also required, but if using a calendar table, intervention
is required by creating the dates, and deleting 2 sets of dates (holidays
and weekends) whereas, by using code one set of dates is added to the
holiday table.

In the situation you mentioned where booking rooms, every day is a booking
day, so all dates will be entered anyway. BTW, that is an excellent SQL
statement.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ken Sheridan said:
I disagree. Enforcement of a constraint via the logical model is always to
be preferred to application dependency. Not only does it allow for
portability, but also (and this is important in a distributed application)
for user maintenance whereas enforcement at form level would require
amendment of the form definition in the event of the 'weekend' being
changed.
The latter is not an unrealistic scenario; some companies here have moved
to
a four-day working week of longer days to reduce overheads, and provide
employees with a longer weekends. With a calendar table all that's
required
if a company stops working Fridays for instance, is to execute an SQL
statement to delete all rows where the weekday is a Friday after the date
when the change is to be implemented.

As for the size of the 'calendar' table, this is not a significant
consideration; less than 3,000 rows over a ten year period is chickenfeed.

Incidentally an alternative method for creating auxiliary calendar tables
is
suggested by Celko; he recommends serially filling a column in a
spreadsheet
application and importing into the database application.

One area where an auxiliary calendar table really comes into play is in
relation to applications such as room reservations (hotels, business
meetings
etc). The following query for instance will find all vacant rooms within
a
date range defined by the parameters (which in a real world application
would
be references to controls on a dialogue form of course).

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DATETIME;
SELECT Rooms.RoomNumber, C1.CalDate
FROM Rooms, Calendar AS C1
WHERE C1.CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND NOT EXISTS
(SELECT *
FROM RoomOccupations, Calendar AS C2
WHERE C2.CalDate >= EntryDate
AND (C2.CalDate <= DepartureDate OR DepartureDate IS NULL)
AND C2.CalDate = C1.CalDate
AND RoomOccupations.RoomNumber = Rooms.RoomNumber);

I also once used this for a dental appointment reservations application
where the 'calendar' contained one row per 'minimum time slot' rather than
per day, the 'room' being a dentist, dental hygienist etc. That required
further extension off course as it was necessary to take account of each
staff member's personal leave days as well as the Sunday (the surgery
worked
Saturday mornings) and public holidays. This can be done simply enough
via
the logical model, however.

Ken Sheridan
Stafford, England

Arvin Meyer said:
Nice code, but may I suggest, as have the others here, that creating and
maintaining a holiday table is much easier and contains only 8 to 10
records
instead of 250 or more per year.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ken Sheridan said:
You can also enforce it by means of a referential operation. This
would
mean
that invalid dates would be prohibited however they are entered, so
control
is not exercised solely at form level, making the application more
bullet-proof. First you'd create a WorkdaysCalendar table, which is
simply a
table of all non-holiday weekdays over whatever period of time is
appropriate. This table can be created by calling the function below
as
follows, which you can do from the debug window (Press Ctrl+G to open
it).
Call the function like so to make a calendar from this year for 10
years
ahead for instance:

MakeCalendar "WorkdaysCalendar", #01/01/2007#, #12/31/2017#, 2,3,4,5,6

This will make a calendar of all weekdays over the period, so you then
need
to delete the rows for holidays. This can be done manually or via a
'delete'
query like so:

PARAMETERS [Enter holiday date:] DATETIME;
DELETE *
FROM WorkdaysCalendar
WHERE calDate = [Enter holiday date:];

Running this query will prompt for a date and then delete the date
entered
at the prompt from the table. A more flexible approach would be to run
a
query like this via a 'Holidays' dialogue form in which one or more
dates
can
be selected in a multiselect list box and then all deleted form the
table
at
a single button click.

Create a relationship between this table and the existing table on the
date
columns and enforce referential integrity. This will prevent any date
not
in
WorkdaysCalendar being entered into the existing table. In your form
you
can
control it at control level by putting this in the BeforeUpdate event
procedure of the date control:

Const conMESSAGE = "The date entered is a weekend or holiday date."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "calDate = #" & Format(ctrl,"mm/dd/yyyy") & "#"

If IsNull(DLookup("calDate", "WorkdaysCalendar", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Date"
Cancel = True
End If

You might well find the table created is useful for other purposes,
e.g.
for
counting all working days between two dates, which you can do with the
DCount
function or in a query, to count the workdays per project in a Projects
table
for instance with ProjectStart and ProjectEnd date columns:

SELECT Project, COUNT(*) AS WorkdaysPerProject
FROM Projects INNER JOIN WordaysCalendar
ON (WorkdaysCalendar.caldate BETWEEN
Projects.ProjectStart AND Projects.ProjectEnd)
GROUP BY Project;

You can of course use the same function to create any other auxiliary
calendar tables which you might need.

Here's the code to create the calendar table. Paste it into any
standard
module in the database and call it as described above:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " &
_
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") &
"#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Ken Sheridan
Stafford, England

:

i am still learning and would like the help of the group. i have a
from
that
has a date field. i will like to restrict the input dates to only
business or
non holiday dates (no weekends). restriction should include a message
that
the date entered is invalid. thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
Taking your first point I don't think that's the case. If I'm running a
business and until now have had a Monday to Friday working week, I'd have
created the table to contain the dates for weekdays only, then deleted the
holiday dates as these become known for n years ahead. If I decide to change
to a four day working week from I January 2008, I'd then delete all Friday
dates from WorkdaysCalendar from 1 January 2008 onwards. If, however, I'd
already scheduled people to work on Fridays next year then the DELETE
statement would fail due to the enforced constraint between the WorkSchedule
table and WorkdaysCalendar until those rows are removed from WorkSchedule,
which is exactly what should be done when the working week is changed of
course, i.e. the constraint is enforced entirely independently of the
interface, and even if the data is moved to an application using a different
interface the integrity of the data is protected.

The same process would apply if a day previously scheduled as a working day
were declared a public holiday, which sometimes happens here for special
anniversaries etc, though there is usually plenty of notice of this, so that
situation would be unlikely to arise in reality.

I don't follow your point about yearly intervention, I'm afraid. I can't
see that designating holidays by excluding them from a table of working days
requires any fundamentally different approach than would be the case by
inserting them into a table of holidays. One is an INSERT INTO statement,
the other a DELETE statement, either of which can be done as soon as the
dates of public holidays are known. The weekend days don't require deletion
BTW if the function is used to create the table as they are not inserted in
the first place if you exclude them from the parameter array argument.

Finally I think its equally true to say of a work schedule that every day in
the calendar table is a working day as it is to say every day in a booking
system is a booking day. The latter might also exclude days, e.g. a
restaurant in Paris is likely to exclude Sundays as they for the most part
don't open then (except in the more touristy areas) whereas here it would be
very unusual for a restaurant not to be open on a Sunday.

There is also the more fundamental question of whether not having a workdays
calendar table complies with Date's Information principle:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.

But as I now have to take the cat to the vet, I'll have to leave any
discussion on that, interesting as it would be, for another day.

Regards

Ken Sheridan
Stafford, England

Arvin Meyer said:
Of course, in your scenario, deleting all the rows would be a mistake since
any existing record that depended on a Friday would no longer be accurate.
So your SQL statement needs to be altered, which also makes it application
dependent. The code I proposed would also need to be altered. The point is
that in either case if there is a change, intervention is required. Yearly
intervention is also required, but if using a calendar table, intervention
is required by creating the dates, and deleting 2 sets of dates (holidays
and weekends) whereas, by using code one set of dates is added to the
holiday table.

In the situation you mentioned where booking rooms, every day is a booking
day, so all dates will be entered anyway. BTW, that is an excellent SQL
statement.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Ken Sheridan said:
I disagree. Enforcement of a constraint via the logical model is always to
be preferred to application dependency. Not only does it allow for
portability, but also (and this is important in a distributed application)
for user maintenance whereas enforcement at form level would require
amendment of the form definition in the event of the 'weekend' being
changed.
The latter is not an unrealistic scenario; some companies here have moved
to
a four-day working week of longer days to reduce overheads, and provide
employees with a longer weekends. With a calendar table all that's
required
if a company stops working Fridays for instance, is to execute an SQL
statement to delete all rows where the weekday is a Friday after the date
when the change is to be implemented.

As for the size of the 'calendar' table, this is not a significant
consideration; less than 3,000 rows over a ten year period is chickenfeed.

Incidentally an alternative method for creating auxiliary calendar tables
is
suggested by Celko; he recommends serially filling a column in a
spreadsheet
application and importing into the database application.

One area where an auxiliary calendar table really comes into play is in
relation to applications such as room reservations (hotels, business
meetings
etc). The following query for instance will find all vacant rooms within
a
date range defined by the parameters (which in a real world application
would
be references to controls on a dialogue form of course).

PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DATETIME;
SELECT Rooms.RoomNumber, C1.CalDate
FROM Rooms, Calendar AS C1
WHERE C1.CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND NOT EXISTS
(SELECT *
FROM RoomOccupations, Calendar AS C2
WHERE C2.CalDate >= EntryDate
AND (C2.CalDate <= DepartureDate OR DepartureDate IS NULL)
AND C2.CalDate = C1.CalDate
AND RoomOccupations.RoomNumber = Rooms.RoomNumber);

I also once used this for a dental appointment reservations application
where the 'calendar' contained one row per 'minimum time slot' rather than
per day, the 'room' being a dentist, dental hygienist etc. That required
further extension off course as it was necessary to take account of each
staff member's personal leave days as well as the Sunday (the surgery
worked
Saturday mornings) and public holidays. This can be done simply enough
via
the logical model, however.

Ken Sheridan
Stafford, England

Arvin Meyer said:
Nice code, but may I suggest, as have the others here, that creating and
maintaining a holiday table is much easier and contains only 8 to 10
records
instead of 250 or more per year.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

You can also enforce it by means of a referential operation. This
would
mean
that invalid dates would be prohibited however they are entered, so
control
is not exercised solely at form level, making the application more
bullet-proof. First you'd create a WorkdaysCalendar table, which is
simply a
table of all non-holiday weekdays over whatever period of time is
appropriate. This table can be created by calling the function below
as
follows, which you can do from the debug window (Press Ctrl+G to open
it).
Call the function like so to make a calendar from this year for 10
years
ahead for instance:

MakeCalendar "WorkdaysCalendar", #01/01/2007#, #12/31/2017#, 2,3,4,5,6

This will make a calendar of all weekdays over the period, so you then
need
to delete the rows for holidays. This can be done manually or via a
'delete'
query like so:

PARAMETERS [Enter holiday date:] DATETIME;
DELETE *
FROM WorkdaysCalendar
WHERE calDate = [Enter holiday date:];

Running this query will prompt for a date and then delete the date
entered
at the prompt from the table. A more flexible approach would be to run
a
query like this via a 'Holidays' dialogue form in which one or more
dates
can
be selected in a multiselect list box and then all deleted form the
table
at
a single button click.

Create a relationship between this table and the existing table on the
date
columns and enforce referential integrity. This will prevent any date
not
in
WorkdaysCalendar being entered into the existing table. In your form
you
can
control it at control level by putting this in the BeforeUpdate event
procedure of the date control:

Const conMESSAGE = "The date entered is a weekend or holiday date."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = "calDate = #" & Format(ctrl,"mm/dd/yyyy") & "#"

If IsNull(DLookup("calDate", "WorkdaysCalendar", strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Date"
Cancel = True
End If

You might well find the table created is useful for other purposes,
e.g.
for
counting all working days between two dates, which you can do with the
DCount
function or in a query, to count the workdays per project in a Projects
table
for instance with ProjectStart and ProjectEnd date columns:

SELECT Project, COUNT(*) AS WorkdaysPerProject
FROM Projects INNER JOIN WordaysCalendar
ON (WorkdaysCalendar.caldate BETWEEN
Projects.ProjectStart AND Projects.ProjectEnd)
GROUP BY Project;

You can of course use the same function to create any other auxiliary
calendar tables which you might need.

Here's the code to create the calendar table. Paste it into any
standard
module in the database and call it as described above:

Public Function MakeCalendar_DAO(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts: Name of calendar table to be created: String.
' Start date for calendar: DateTime.
' End date for calendar: DateTime.
' Days of week to be included in calendar
' as value list, e,g 2,3,4,5,6 for Mon-Fri
' (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strTable & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & "(calDate) " &
_
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") &
"#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Ken Sheridan
Stafford, England

:

i am still learning and would like the help of the group. i have a
from
that
has a date field. i will like to restrict the input dates to only
business or
non holiday dates (no weekends). restriction should include a message
that
the date entered is invalid. thanks for your help
Click to expand...
Click to expand...
Click to expand...
 
Back
Top