Print Page | Close Window

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

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Long, Crystal
Forum Description: Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
Printed Date: 16 Nov 2018 at 10:19pm

Topic: Dates, Holidays, Wkends,Workday Hrs, Fiscal Yr ,,,
Posted By: Crystal Long
Subject: Dates, Holidays, Wkends,Workday Hrs, Fiscal Yr ,,,
Date Posted: 25 May 2012 at 2:47pm
uploads/84/ - uploads/84/

Dates, Holidays, Weekends, Calendar Row & Column, occurrence of DOW in month, 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 want to know things like when is the 3rd Wednesday every month?

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? ... fill StartDay and EndDay even if you use midnight as your start of day. You will use criteria to get the right record in Dates -- then you can link on Dat (date) to your other tables.  This is especially useful for outer joins and finding missing information.

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).

RowN & ColN are the calculated row number and column number for generating calendars based on Sunday being the first day listed.

DOWn is the occurrence number of the DayOfWeek in the month (ie: 1st Monday, 4th Thursday).  There is a function to generate a cardinal number in the module -- ie: 1st, 3rd, 4th, 100th.

Date/time fields for StartDay and EndDay when workday is not midnight to midnight (or even if it is).  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.

Feedback is appreciated, thank you

Warm Regards,

Microsoft MVP
remote programming and training

Access Basics by Crystal -
Free 100-page book that covers essentials in Access -

   (: have an awesome day :)

Warm Regards,
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together
~have an awesome day ~

Print Page | Close Window