Date Validation

[ramdil]

Hi All
I have a small requirement.I have column called payment date in my system.Now i want a validation as follows.
when someone enters a payment date for over a weekend or bank holiday then it show a message its a holiday.How can it be done.I know some weekday functions are available but will it give me bank holidays or sat or sundays..Can any please give some idea on it.One idea in my mind is to create a table which stores all these dates and validate against it,but i feel it is not so proffessional.
Thanks in advance

[Stewart Ross]

Hi Ramdil. You have two requirements here. The first relates to Sundays and Saturdays, which can be done by checking the day for the date entered using the Weekday function to return the day, as the skeleton here shows:

Dim DayofWeek as Integer
DayofWeek = WeekDay(somedate)
If DayofWeek = vbSaturday or DayofWeek = vbSunday then
  {do stuff}
else
  {do something else}
endif

For bank holidays there is no choice but to use a table holding the specific dates, as these are not in any way under Access's control. Bank holiday dates vary each year, and some vary locally according to region. There is nothing unprofessional about using lookup tables for such values - there is no other way to do so.

-Stewart

[nico5038]

This link may provide some insight as it's calculating working days with such a holiday table:
http://www.mvps.org/access/datetime/date0006.htm

Very nice other samples there too :-)

Nic;o)

[ramdil]

Hi Thanks for you valuable help.

Hi Ramdil. You have two requirements here. The first relates to Sundays and Saturdays, which can be done by checking the day for the date entered using the Weekday function to return the day, as the skeleton here shows:

Dim DayofWeek as Integer
DayofWeek = WeekDay(somedate)
If DayofWeek = vbSaturday or DayofWeek = vbSunday then
  {do stuff}
else
  {do something else}
endif

For bank holidays there is no choice but to use a table holding the specific dates, as these are not in any way under Access's control. Bank holiday dates vary each year, and some vary locally according to region. There is nothing unprofessional about using lookup tables for such values - there is no other way to do so.

-Stewart