Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Long, Crystal
  New Posts New Posts RSS Feed - Dates, Holidays, Wkends,Workday Hrs, Fiscal Yr ,,,
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

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

 Post Reply Post Reply
Crystal Long View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 35
Post Options Post Options   Thanks (0) Thanks(0)   Quote Crystal Long Quote  Post ReplyReply Direct Link To This Post Topic: Dates, Holidays, Wkends,Workday Hrs, Fiscal Yr ,,,
    Posted: 25 May 2012 at 2:47pm

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 ~
Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down