
Dates, Holidays, Wkends, Fiscal Year,,,
Dates, Holidays, Wkends,Workday Hrs, Fiscal Yr ,,,

Crystal
strive4peace2010@yahoo.com


Dates, Holidays, Weekends, Calendar Row & Column, Workday Hours, Fiscal Year


Having a Dates table in an Access database can streamline things if you answer Yes to any of these questions:

Do you have a workday other than midnight to midnight?

Does your fiscal year start on a day other than January 1?

Do you need to ignore weekends or holidays?

Do you need to find dates where data is missing?

Do you need to generate calendars?

Do you track date and time in a field but want to link on date only without having to modify the SQL and give up making any further changes in the designer?

This is an Access 2000 database that has one table called Dates with records for each date from 1990 to 2050. 

IsWorkday Checkbox is false if date is on a weekend.  This is stored as an integer not a byte so it can hold a null.  DisplayControl was set to checkbox using code that can be found on Allen Browne's site. You can copy this field to create integer fields that are checkboxes in other tables.

DayName & HolidayNote fields -- American holidays marked for 2007-2012; logic can be extrapolated to future.   HolidayNote tells the rule for the holiday (ie: Jan 1, 4th Thursday in November).

Row and column is calculated for generating calendars.

Date/time fields for StartDay and EndDay when workday is not midnight to midnight. Run update queries to populate these fields if needed.

FY field useful if fiscal year starts on a day other than Jan 1.  Run an update query to populate this.

Also included is a module with code to make more records in the Dates table.  You will then need to run update queries for the other calculated fields that are stored.  Sometimes I store DOW (DayOfWeek) in the Dates table too if there are comboboxes that need, for instance, only Mondays to be listed.  This can be calculated ... but it never changes so why not have it ready and available for filtering too?

I added a field for FY.  You can use update queries to put the information in. 

Delete the extra fields that you do not need from the Dates table to make it faster.  Reduce the number of records it has for the same reason.  If you need stuff back again, you can import the table again.  Build indexes on fields that you search to be faster.

