calculating Holiday dates for multiple countries

[Devon]

Hello

I am attempting to create a database for resource allocations within our
company. Our company has employees worldwide, and so to have the most
accurate data, I will need to exclude holiday dates for the various
countries.

I currently have multiple tables, one of which is Countries, and another
which is Dates. For Dates, I have the following fields: FromDate, ToDate,
and Date (field is concatenated FromDate and ToDate for easier data entry and
to limit data entry error). I then have the Date field as a datasheet on a
subform.

I have seen a few postings out on this forum where some VBA code has been
included to skip the weekends, and also to skip holidays (once a seperate
Holiday table has been created which includes the holiday dates), but never
one that includes multiple countries.

Does anyone have a suggestion on how to get this to work? I am fairly
flexible concerning solutions, as long as the data will be easy to update and
maintain.

Thanks in advance.

Devon

 

[Clifford Bass]

Hi Devon,

In your holidays table, you will include a country (code) column as
part of the primary key.

tblHolidays
Country_Code
Holiday_Date

You will need to enter all holidays for every country separately.
Well, I suppose you could also have a dummy country (DMY) for holidays that
span all countries (New Years?) so you do not need to enter that 200+ times.
Then when checking to see if a date is a holiday, you will include the
country (code) as part of the condition.

select *
from tblHolidays
where (Country_Code = 'CAN' or Country_Code = 'DMY') and Holiday_Date =
#01/01/2009#

Other than that, it would all be essentially the same.

Clifford Bass

 

[Devon]

Clifford

Thanks for the assistance. I do need a little more help.

I have created tblHolidays as you stated.

I was thinking that I would need to create an If/Then statement when
entering the From and To Dates in the Dates table (Dates table is updated
after the Countries table). I would then create numerous If/Then/ElseIf
statements that would go through and look for the various dates by Country.
For example, one might be If [Country_Name] = 'United States', then...

My problem, is I am not sure what to put in the Then part of the statement.
The Holiday date could be in the From Date, To Date, or a date in between the
two dates. If the holiday date is during the time period (From, To, or in
between) then I want to exclude it from the time period. For example, in the
United States, Christmas is on Friday, December 25, 2009. If I were to go
into December and the From Date is #12/21/2009# and To Date is #12/25/2009#,
then my date range for that week would only be #12/21/2009# through
#12/24/2009#. It would also be nice to include a pop-up message that would
say something like 'During this time period, #12/25/2009# was not included as
it is a Holiday in the country you have chosen. To see what holiday, please
see the tblHoliday.

Thanks

Devon

 

[Clifford Bass]

Hi Devon,

One thing first before getting to a possible solution. You mention a
Dates table and a field named Date. I would suggest renaming those to be
more specific. Date is a reserved word (think Date() function), and its use
for something else can lead to unexpected results. So maybe rename it to
something like FromAndToDates. Do a search in Access's online help for
"reserved words" to get a list of other reserved words to avoid using as
object names.

If I understand, you want to check a date range someone enters into a
field on a form. Is the country also on the form? If so it becomes pretty
simple. Once you have the separate from and to dates you loop through them,
one day at a time, checking first to see if each is a week-end day (if
excluding week-end days) and if a holiday. To check if a holiday, you would
use the country field on the form along with the dates. Untested code,
assumes an added field in tblHolidays named "Holiday Description"; "txt..."
items are fields on your form:

Dim dtToCheck As Date
Dim varHolidayDescription As Variant

For dtToCheck = [txtFromDate] To [txtToDate]
If WeekDay(dtToCheck) >= 2 And WeekDay(dtToCheck) <= 6 Then
' A weekday - is it a holiday
varHolidayDescription = DLookup("tblHolidays",
"Holiday_Description", _
"Country_Name = """ & [txtCountry_Name] & """ and " & _
"Holiday_Date = #" & dtToCheck & "#")
If IsNull(varHolidayDescription) Then
' Not a holiday - count it

Else
' A holiday - do not count
MsgBox "Skipping " & varHolidayDescription & "."
End If
Else
' A week-end day - do not count
End If
Next dtToCheck

If the country is not on the form, you will have to get it from the
wherever it is that it is specified.

Hope that helps,

Clifford Bass

Clifford

Thanks for the assistance. I do need a little more help.

I have created tblHolidays as you stated.

I was thinking that I would need to create an If/Then statement when
entering the From and To Dates in the Dates table (Dates table is updated
after the Countries table). I would then create numerous If/Then/ElseIf
statements that would go through and look for the various dates by Country.
For example, one might be If [Country_Name] = 'United States', then...

My problem, is I am not sure what to put in the Then part of the statement.
The Holiday date could be in the From Date, To Date, or a date in between the
two dates. If the holiday date is during the time period (From, To, or in
between) then I want to exclude it from the time period. For example, in the
United States, Christmas is on Friday, December 25, 2009. If I were to go
into December and the From Date is #12/21/2009# and To Date is #12/25/2009#,
then my date range for that week would only be #12/21/2009# through
#12/24/2009#. It would also be nice to include a pop-up message that would
say something like 'During this time period, #12/25/2009# was not included as
it is a Holiday in the country you have chosen. To see what holiday, please
see the tblHoliday.

Thanks

Devon

 

[Steve Sanford]

FYI, Allen Browne has a list of reserved words that cover:

* Reserved words in Access
* Reserved words in Jet 4.0
* ANSI-92 Reserved Words
* Reserved in MS Query
* Reserved Keywords in SQL Server


http://allenbrowne.com/AppIssueBadWord.html


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Devon,

One thing first before getting to a possible solution. You mention a
Dates table and a field named Date. I would suggest renaming those to be
more specific. Date is a reserved word (think Date() function), and its use
for something else can lead to unexpected results. So maybe rename it to
something like FromAndToDates. Do a search in Access's online help for
"reserved words" to get a list of other reserved words to avoid using as
object names.

If I understand, you want to check a date range someone enters into a
field on a form. Is the country also on the form? If so it becomes pretty
simple. Once you have the separate from and to dates you loop through them,
one day at a time, checking first to see if each is a week-end day (if
excluding week-end days) and if a holiday. To check if a holiday, you would
use the country field on the form along with the dates. Untested code,
assumes an added field in tblHolidays named "Holiday Description"; "txt..."
items are fields on your form:

Dim dtToCheck As Date
Dim varHolidayDescription As Variant

For dtToCheck = [txtFromDate] To [txtToDate]
If WeekDay(dtToCheck) >= 2 And WeekDay(dtToCheck) <= 6 Then
' A weekday - is it a holiday
varHolidayDescription = DLookup("tblHolidays",
"Holiday_Description", _
"Country_Name = """ & [txtCountry_Name] & """ and " & _
"Holiday_Date = #" & dtToCheck & "#")
If IsNull(varHolidayDescription) Then
' Not a holiday - count it

Else
' A holiday - do not count
MsgBox "Skipping " & varHolidayDescription & "."
End If
Else
' A week-end day - do not count
End If
Next dtToCheck

If the country is not on the form, you will have to get it from the
wherever it is that it is specified.

Hope that helps,

Clifford Bass

Clifford

Thanks for the assistance. I do need a little more help.

I have created tblHolidays as you stated.

I was thinking that I would need to create an If/Then statement when
entering the From and To Dates in the Dates table (Dates table is updated
after the Countries table). I would then create numerous If/Then/ElseIf
statements that would go through and look for the various dates by Country.
For example, one might be If [Country_Name] = 'United States', then...

My problem, is I am not sure what to put in the Then part of the statement.
The Holiday date could be in the From Date, To Date, or a date in between the
two dates. If the holiday date is during the time period (From, To, or in
between) then I want to exclude it from the time period. For example, in the
United States, Christmas is on Friday, December 25, 2009. If I were to go
into December and the From Date is #12/21/2009# and To Date is #12/25/2009#,
then my date range for that week would only be #12/21/2009# through
#12/24/2009#. It would also be nice to include a pop-up message that would
say something like 'During this time period, #12/25/2009# was not included as
it is a Holiday in the country you have chosen. To see what holiday, please
see the tblHoliday.

Thanks

Devon

 

[Devon]

Clifford

Thanks for the additional information. I have changed the name of the field
from Date.

Here is what I think might work best....let me know if you agree.

I have created a form (frmCountry) and a subform (frmdate_subform) which are
linked together. I would like to use this form/subform combo for date entry
using the code you listed below.

frmCountry contains the following fields: CRID (PK), Region (Continent),
and Country (Country).

frmdate_subform contains the following fields: DateID (PK), From_Date,
To_Date, Location (Country), and CRID (FK),

I then have a command button at the bottom of the form, and have entered
your code 'On Click'. I believe that your code would tell me if the date
range I chose contains a holiday or weekend date, and then I could make sure
to exclude that date, correct?

Once this code has run and the From_Date and To_Date have been updated for
all the countries, I would then run an Update Query to concatenate the
From_Date and To_Date to make FromTo_Date within this same table looking for
Null values in the FromTo_Date field. I would then have the FromTo_Date and
Location (Country) data on the same table.

I could then use the FromTo_Date and Location as a Lookup fields in another
Date table, which would be entered by the User. The User would go through
and choose the Project, the Country, the Dates (which I would have linked as
an equi-join from Country to Dates using the Location to limit the dates
viewable by the user), and the Activities.

Hopefully this makes sense.

Also, I have some more questions concerning your code. I am getting the
following error:

Run-time error '2465':

Microsoft Office Access can't find the field "|" referred to in your
expression.

The part of the code it doesn't like is as follows:

varHolidayDescription = DLookup("tblHolidays", "Holiday_Description", _
"Country_Name = """ & [txtCountry_Name] & """ and " & _
"Holiday_Date = #" & dtToCheck & "#")

I suspect it is because my tblHolidays doesn't contain all the same fields
as is listed in the code. In looking at the code, I am not entirely sure
what txtCountry_Name is, or is doing.

For my tblHolidays, it contains the following fields:

HolID (PK), Holiday_Date, Country_Name, and Holiday_Description.

Thanks in advance for any advice/assistance you can offer. I sincerely
appreciate it.

Devon

Hi Devon,

One thing first before getting to a possible solution. You mention a
Dates table and a field named Date. I would suggest renaming those to be
more specific. Date is a reserved word (think Date() function), and its use
for something else can lead to unexpected results. So maybe rename it to
something like FromAndToDates. Do a search in Access's online help for
"reserved words" to get a list of other reserved words to avoid using as
object names.

If I understand, you want to check a date range someone enters into a
field on a form. Is the country also on the form? If so it becomes pretty
simple. Once you have the separate from and to dates you loop through them,
one day at a time, checking first to see if each is a week-end day (if
excluding week-end days) and if a holiday. To check if a holiday, you would
use the country field on the form along with the dates. Untested code,
assumes an added field in tblHolidays named "Holiday Description"; "txt..."
items are fields on your form:

Dim dtToCheck As Date
Dim varHolidayDescription As Variant

For dtToCheck = [txtFromDate] To [txtToDate]
If WeekDay(dtToCheck) >= 2 And WeekDay(dtToCheck) <= 6 Then
' A weekday - is it a holiday
varHolidayDescription = DLookup("tblHolidays",
"Holiday_Description", _
"Country_Name = """ & [txtCountry_Name] & """ and " & _
"Holiday_Date = #" & dtToCheck & "#")
If IsNull(varHolidayDescription) Then
' Not a holiday - count it

Else
' A holiday - do not count
MsgBox "Skipping " & varHolidayDescription & "."
End If
Else
' A week-end day - do not count
End If
Next dtToCheck

If the country is not on the form, you will have to get it from the
wherever it is that it is specified.

Hope that helps,

Clifford Bass

Clifford

Thanks for the assistance. I do need a little more help.

I have created tblHolidays as you stated.

I was thinking that I would need to create an If/Then statement when
entering the From and To Dates in the Dates table (Dates table is updated
after the Countries table). I would then create numerous If/Then/ElseIf
statements that would go through and look for the various dates by Country.
For example, one might be If [Country_Name] = 'United States', then...

My problem, is I am not sure what to put in the Then part of the statement.
The Holiday date could be in the From Date, To Date, or a date in between the
two dates. If the holiday date is during the time period (From, To, or in
between) then I want to exclude it from the time period. For example, in the
United States, Christmas is on Friday, December 25, 2009. If I were to go
into December and the From Date is #12/21/2009# and To Date is #12/25/2009#,
then my date range for that week would only be #12/21/2009# through
#12/24/2009#. It would also be nice to include a pop-up message that would
say something like 'During this time period, #12/25/2009# was not included as
it is a Holiday in the country you have chosen. To see what holiday, please
see the tblHoliday.

Thanks

Devon

 

[Douglas J. Steele]

See whether my June, 2005 "Access Answers" column in Pinnacle Publication's
"Smart Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Clifford

Thanks for the assistance. I do need a little more help.

I have created tblHolidays as you stated.

I was thinking that I would need to create an If/Then statement when
entering the From and To Dates in the Dates table (Dates table is updated
after the Countries table). I would then create numerous If/Then/ElseIf
statements that would go through and look for the various dates by
Country.
For example, one might be If [Country_Name] = 'United States', then...

My problem, is I am not sure what to put in the Then part of the
statement.
The Holiday date could be in the From Date, To Date, or a date in between
the
two dates. If the holiday date is during the time period (From, To, or in
between) then I want to exclude it from the time period. For example, in
the
United States, Christmas is on Friday, December 25, 2009. If I were to go
into December and the From Date is #12/21/2009# and To Date is
#12/25/2009#,
then my date range for that week would only be #12/21/2009# through
#12/24/2009#. It would also be nice to include a pop-up message that
would
say something like 'During this time period, #12/25/2009# was not included
as
it is a Holiday in the country you have chosen. To see what holiday,
please
see the tblHoliday.

Thanks

Devon

Hi Devon,

In your holidays table, you will include a country (code) column as
part of the primary key.

tblHolidays
Country_Code
Holiday_Date

You will need to enter all holidays for every country separately.
Well, I suppose you could also have a dummy country (DMY) for holidays
that
span all countries (New Years?) so you do not need to enter that 200+
times.
Then when checking to see if a date is a holiday, you will include the
country (code) as part of the condition.

select *
from tblHolidays
where (Country_Code = 'CAN' or Country_Code = 'DMY') and Holiday_Date =
#01/01/2009#

Other than that, it would all be essentially the same.

Clifford Bass

 

[Devon]

Doug

I think that it is a nice temporary solution. It will work for now, as I
can run the dbase you created while entering data in my dbase. It will allow
me to manually exclude the holiday dates from the mix for the various
countries.

Thanks for the information.

Devon

See whether my June, 2005 "Access Answers" column in Pinnacle Publication's
"Smart Access" helps.

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Clifford

Thanks for the assistance. I do need a little more help.

I have created tblHolidays as you stated.

I was thinking that I would need to create an If/Then statement when
entering the From and To Dates in the Dates table (Dates table is updated
after the Countries table). I would then create numerous If/Then/ElseIf
statements that would go through and look for the various dates by
Country.
For example, one might be If [Country_Name] = 'United States', then...

My problem, is I am not sure what to put in the Then part of the
statement.
The Holiday date could be in the From Date, To Date, or a date in between
the
two dates. If the holiday date is during the time period (From, To, or in
between) then I want to exclude it from the time period. For example, in
the
United States, Christmas is on Friday, December 25, 2009. If I were to go
into December and the From Date is #12/21/2009# and To Date is
#12/25/2009#,
then my date range for that week would only be #12/21/2009# through
#12/24/2009#. It would also be nice to include a pop-up message that
would
say something like 'During this time period, #12/25/2009# was not included
as
it is a Holiday in the country you have chosen. To see what holiday,
please
see the tblHoliday.

Thanks

Devon

Hi Devon,

In your holidays table, you will include a country (code) column as
part of the primary key.

tblHolidays
Country_Code
Holiday_Date

You will need to enter all holidays for every country separately.
Well, I suppose you could also have a dummy country (DMY) for holidays
that
span all countries (New Years?) so you do not need to enter that 200+
times.
Then when checking to see if a date is a holiday, you will include the
country (code) as part of the condition.

select *
from tblHolidays
where (Country_Code = 'CAN' or Country_Code = 'DMY') and Holiday_Date =
#01/01/2009#

Other than that, it would all be essentially the same.

Clifford Bass

:

Hello

I am attempting to create a database for resource allocations within
our
company. Our company has employees worldwide, and so to have the most
accurate data, I will need to exclude holiday dates for the various
countries.

I currently have multiple tables, one of which is Countries, and
another
which is Dates. For Dates, I have the following fields: FromDate,
ToDate,
and Date (field is concatenated FromDate and ToDate for easier data
entry and
to limit data entry error). I then have the Date field as a datasheet
on a
subform.

I have seen a few postings out on this forum where some VBA code has
been
included to skip the weekends, and also to skip holidays (once a
seperate
Holiday table has been created which includes the holiday dates), but
never
one that includes multiple countries.

Does anyone have a suggestion on how to get this to work? I am fairly
flexible concerning solutions, as long as the data will be easy to
update and
maintain.

Thanks in advance.

Devon

 

[Clifford Bass]

Hi Devon,

Answers interspersed below.

Clifford Bass

Clifford

Thanks for the additional information. I have changed the name of the field
from Date.

Here is what I think might work best....let me know if you agree.

I have created a form (frmCountry) and a subform (frmdate_subform) which are
linked together. I would like to use this form/subform combo for date entry
using the code you listed below.

frmCountry contains the following fields: CRID (PK), Region (Continent),
and Country (Country).

frmdate_subform contains the following fields: DateID (PK), From_Date,
To_Date, Location (Country), and CRID (FK),

I then have a command button at the bottom of the form, and have entered
your code 'On Click'. I believe that your code would tell me if the date
range I chose contains a holiday or weekend date, and then I could make sure
to exclude that date, correct?
Correct.

Once this code has run and the From_Date and To_Date have been updated for
all the countries, I would then run an Update Query to concatenate the
From_Date and To_Date to make FromTo_Date within this same table looking for
Null values in the FromTo_Date field. I would then have the FromTo_Date and
Location (Country) data on the same table.

I could then use the FromTo_Date and Location as a Lookup fields in another
Date table, which would be entered by the User. The User would go through
and choose the Project, the Country, the Dates (which I would have linked as
an equi-join from Country to Dates using the Location to limit the dates
viewable by the user), and the Activities.

As it looks like your goal is to provide a list of date ranges, from
which the user selects, it may simplify things to get rid of the FromTo_Date
column and just construct the value on the fly, as a calculated field,
whenever needed. Otherwise, you are dealing with making sure that field is
correct whenever you make changes to either of the date fields. You could
create a query that that just returns all of the rows from the table and
includes the calculated column. Then you would just use the query wherever
you now use the table.

Hopefully this makes sense.

It seems to to me. One comment, which probably is not necessary: You
will need to adjust the code to deal with getting your country from the main
form and the dates from the subform.

Also, I have some more questions concerning your code. I am getting the
following error:

Run-time error '2465':

Microsoft Office Access can't find the field "|" referred to in your
expression.

The part of the code it doesn't like is as follows:

varHolidayDescription = DLookup("tblHolidays", "Holiday_Description", _
"Country_Name = """ & [txtCountry_Name] & """ and " & _
"Holiday_Date = #" & dtToCheck & "#")

I suspect it is because my tblHolidays doesn't contain all the same fields
as is listed in the code. In looking at the code, I am not entirely sure
what txtCountry_Name is, or is doing.

It is probably complaining about the "[txtCountry_Name]" item. You
will need to change it to reflect the actual text box name on your form,
which from what you stated above would be "Location".

 

[Devon]

Clifford

Thanks again for the response. I agree with what you said.

I also think I am nearly done with your code.

I updated the code as follows, and it works like a charm:

varHolidayDescription = DLookup("Holiday_Description", "tblHoliday", _
"Country_Name = """ & [frmDate_DataEntry_Subform].Form![Location] & """
and " & _
"Holiday_Date = #" & dtToCheck & "#").

My final question for this post, is, I would like the Msgbox to contain the
date of the holiday. Would the variable 'dtToCheck' still be stored in
memory?

Also, how would I add it to the following statement (seems much easier in
Java than it is in MS Access).

MsgBox "Skipping " & varHolidayDescription & "."

Thanks again

Devon

Hi Devon,

Answers interspersed below.

Clifford Bass

Clifford

Thanks for the additional information. I have changed the name of the field
from Date.

Here is what I think might work best....let me know if you agree.

I have created a form (frmCountry) and a subform (frmdate_subform) which are
linked together. I would like to use this form/subform combo for date entry
using the code you listed below.

frmCountry contains the following fields: CRID (PK), Region (Continent),
and Country (Country).

frmdate_subform contains the following fields: DateID (PK), From_Date,
To_Date, Location (Country), and CRID (FK),

I then have a command button at the bottom of the form, and have entered
your code 'On Click'. I believe that your code would tell me if the date
range I chose contains a holiday or weekend date, and then I could make sure
to exclude that date, correct?
Correct.

Once this code has run and the From_Date and To_Date have been updated for
all the countries, I would then run an Update Query to concatenate the
From_Date and To_Date to make FromTo_Date within this same table looking for
Null values in the FromTo_Date field. I would then have the FromTo_Date and
Location (Country) data on the same table.

I could then use the FromTo_Date and Location as a Lookup fields in another
Date table, which would be entered by the User. The User would go through
and choose the Project, the Country, the Dates (which I would have linked as
an equi-join from Country to Dates using the Location to limit the dates
viewable by the user), and the Activities.

As it looks like your goal is to provide a list of date ranges, from
which the user selects, it may simplify things to get rid of the FromTo_Date
column and just construct the value on the fly, as a calculated field,
whenever needed. Otherwise, you are dealing with making sure that field is
correct whenever you make changes to either of the date fields. You could
create a query that that just returns all of the rows from the table and
includes the calculated column. Then you would just use the query wherever
you now use the table.

Hopefully this makes sense.

It seems to to me. One comment, which probably is not necessary: You
will need to adjust the code to deal with getting your country from the main
form and the dates from the subform.

Also, I have some more questions concerning your code. I am getting the
following error:

Run-time error '2465':

Microsoft Office Access can't find the field "|" referred to in your
expression.

The part of the code it doesn't like is as follows:

varHolidayDescription = DLookup("tblHolidays", "Holiday_Description", _
"Country_Name = """ & [txtCountry_Name] & """ and " & _
"Holiday_Date = #" & dtToCheck & "#")

I suspect it is because my tblHolidays doesn't contain all the same fields
as is listed in the code. In looking at the code, I am not entirely sure
what txtCountry_Name is, or is doing.

It is probably complaining about the "[txtCountry_Name]" item. You
will need to change it to reflect the actual text box name on your form,
which from what you stated above would be "Location".

For my tblHolidays, it contains the following fields:

HolID (PK), Holiday_Date, Country_Name, and Holiday_Description.

Thanks in advance for any advice/assistance you can offer. I sincerely
appreciate it.

Devon

 

[Clifford Bass]

Hi Devon,

You are welcome.

Yes, at the time of the message box, as long as you are keeping it in
the for/next loop, the dtToCheck would still be available. Quite easy to add
it since Access does a lot of implicit conversions. Things like numbers and
dates to strings, or even the opposite:

MsgBox "Skipping " & varHolidayDescription & " (" & dtToCheck & ")."

This would produce (depending on your computer's regional settings:

Skipping Christmas (12/25/2009).

Or, if you want it in a longer form:

MsgBox "Skipping " & Format$(dtToCheck, "Long Date") & ", which is " & _
varHolidayDescription & " in [the] " & _
[frmDate_DataEntry_Subform].Form![Location] & "."

Which might produce:

Skipping Friday, December 25, 2009, which is Christmas in [the] United States.

Clifford Bass

 

[Devon]

Clifford

That was exactly what I needed. Thanks again for all your assistance. Very
much appreciated.

Devon

Hi Devon,

You are welcome.

Yes, at the time of the message box, as long as you are keeping it in
the for/next loop, the dtToCheck would still be available. Quite easy to add
it since Access does a lot of implicit conversions. Things like numbers and
dates to strings, or even the opposite:

MsgBox "Skipping " & varHolidayDescription & " (" & dtToCheck & ")."

This would produce (depending on your computer's regional settings:

Skipping Christmas (12/25/2009).

Or, if you want it in a longer form:

MsgBox "Skipping " & Format$(dtToCheck, "Long Date") & ", which is " & _
varHolidayDescription & " in [the] " & _
[frmDate_DataEntry_Subform].Form![Location] & "."

Which might produce:

Skipping Friday, December 25, 2009, which is Christmas in [the] United States.

Clifford Bass

Clifford

Thanks again for the response. I agree with what you said.

I also think I am nearly done with your code.

I updated the code as follows, and it works like a charm:

varHolidayDescription = DLookup("Holiday_Description", "tblHoliday", _
"Country_Name = """ & [frmDate_DataEntry_Subform].Form![Location] & """
and " & _
"Holiday_Date = #" & dtToCheck & "#").

My final question for this post, is, I would like the Msgbox to contain the
date of the holiday. Would the variable 'dtToCheck' still be stored in
memory?

Also, how would I add it to the following statement (seems much easier in
Java than it is in MS Access).

MsgBox "Skipping " & varHolidayDescription & "."

Thanks again

Devon

 

[Clifford Bass]

Hi Devon,

Great! Glad to help.

Clifford Bass