Running Code Automatically on First Workday of the Month

  • Thread starter Thread starter Denis Bisson via AccessMonster.com
  • Start date Start date
D

Denis Bisson via AccessMonster.com

Good day,
I would like to automatically run code (which will export a report to a
..rtf file which will then be emailed) on the First Workday of the month.

The code that I want to run (via an Autoexec macro) should not be a
problem; however the assistance I am seeking is for a way (probably code)
for the system to recognize that the current day is in fact, the first
WORKDAY of the month.

The database is opened daily.

I have read some posts in this forum that suggested using the Windows Task
Scheduler. While this sounds extremely logical, our network security
settings do not permit us to use it :-(

Thank you for taking the time to read this and, where appropriate,
providing me with any suggestions.

Denis
 
This will do it for you. It includes a look up to a holiday table to see if
the weekday
selected is a holiday. You can either remove the code that looks for the
holiday,
add a holiday table, or change the names I use for it to whatever you
already have.

Function FirstWorkDay(dtmBaseDate As Date) As Date
Dim intX As Integer
Dim blnFoundIt As Boolean
'Compliments of Dave Hargis
'Determines the first working day of the month for the date passed

'Set the date to the 1st of the month
dtmBaseDate = DateSerial(year(dtmBaseDate), Month(dtmBaseDate), 1)
Do Until blnFoundIt
Do Until Weekday(dtmBaseDate, vbMonday) < 6
'If Saturday or Sunday, add a day
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
Loop
'If it is a weekday, see if it is a holiday
If IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmBaseDate & "#")) Then ' Not a holiday
blnFoundIt = True
Else
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
End If
Loop
FirstWorkDay = dtmBaseDate
End Function
 
Thank you. I look forward to implementing this.
Cheers!
Denis
 
Back
Top