First Working Day in the Month

I need a slick way of determining if a given day is the first working day of a given month and if it is, take a set of actions. The only way that I can think of accomplishing this is to store the date once the condition has been met and then check each day to see if the action has already taken place during that month.

This code needs to be part of a set of End-Of-Day routines that are manually triggered.

I thought also about building a table with the first working day in each month. However, if inclement weather were to cause office closure, the set of actions for the month would never take place.

Any slick ideas as my approach seems a little kludgy.

Thanks for the help.
LVL 3
dwcummingsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If the first day of the month is Saturday or Sunday, you can add 2 or 1 to get to the first working day.  But, you also need to keep a table of holidays and adjust the first day yet again if it falls on a holiday.

If what you really want to do is to run something ONCE, the first time someone opens an app in the month, then simply keep a log table.  Log the year and month or full date if you want when the process runs.  Then each time the app opens, check the log.  If the process has already run for the month, open as normal.  If not, run it, log it, then continue.

This method avoids the problems of unexpected closures or forgetting to maintain the holiday table.

I've attached a sample database with some useful date functions.
UsefulDateFunctions120212Call.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwcummingsAuthor Commented:
Excellent. Thanks for the date functions database. I will certainly make good use of them in the future.
0
aikimarkCommented:
This seems to be a slick calculation:
somedate = #3/15/2015#
firstofmonth = dateserial(year(somedate), month(somedate), 1)
firstworkday = firstofmonth + ((vbmonday-weekday(firstofmonth) mod 7) * -(weekday(firstofmonth, vbMonday) > 5))

Open in new window

Then you only need to compare your somedate variable to the firstworkday variable to determine whether you do your action:
Example:
If somedate = firstworkday Then
    'Do some first-of-month actions here
End If

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.