Bacon Bits:

Suppose you've been asked to provide some reporting in Access, but only for those dates that are company workdays (workdays are defined as days that are not weekends or holidays). In Excel, you have the NETWORKDAYS function to help you out. But in Access, you're on your own. There is no such function in Access.

Well, it's your lucky day.  Today, I'll show you an easy way to query only workdays in Access.

The first thing you need to accomplish this task is a table that lists all the company holidays. A 'holidays' table can be nothing more than one field listing all the dates that constitute a holiday.

Once you have established a table that contains all the company holidays, it's time to build the query. The screenshot here demonstrates how to build a query that filters out non-workdays.

 Take a moment to analyze what is going on here:

A)  You first create a left join from your main table to your HolidaysMaster, telling Access that you want all the records from your main table.

B)  You then use the Is Null criteria under Holidays. This limits your main table to only those dates that do not match any of the holidays listed in the HolidaysMaster.

C)  You then create a field called Day Check where you use the WEEKDAY function to return the weekday of every service date. The WEEKDAY function returns the day of the week from a date. In Access weekdays are, by default, numbered from 1 to 7 starting with Sunday. Therefore, if the WEEKDAY function returns the number 1, you know the date represented is a Sunday. If the WEEKDAY function returns a 7, then the date represented is a Saturday.

D)  Finally, you filter the newly created Day Check field to filter out those weekdays that represent Saturdays and Sundays (1 and 7).

There you have it – one query to get to workdays in Access.

• Previous Entry: Running an Access Parameter Query from Excel
• Next Entry: Halloween Costume Day