Blue Monday

[DewMan]

I have an access 2k db that is opened every weekday am. I would like to check for the 1st Monday of every month to do some monthly calculations.

 

[bradcccs]

Rank but functional

On_Open of splash / main menu etc

If Format(Date, "ddd") = "Mon" And Format(Date, "d") <= 7 Then
    ' Your action goes here
End If

If the db is opened more than once, the event will reoccur, but you could work around that if needed.

 

[DewMan]

Great! I knew there was an easier way. I was trying to use weekdays as 'SELECT CASES' and making it very confusing...I just about had it working but had about 15 lines of code...
Thanks a lot!!!

 

[Rich]

Or
If Weekday(Date)=2 And And Format(Date, "d") <= 7 Then

 

[DewMan]

OR
If Weekday(Date) = 2 And Day(Date) <= 7 Then


OH YEAH!!! I GOT IT NOW...
I'M A CERTIFIED DATEOLOGIST.....THANKS AGAIN GUYS

 

[bradcccs]

We have created a monster

 

[Dgar007]

Not to be the fly in the ointment, but what if the 1st Monday of the month is a Holiday and the program does not run?

I'm thinking I may have to create a Holiday table to be maintained each year and then check that before blowing by this snippet of code on a Tuesday.

Is there a better way?

Thanks,

 

[antomack]

The following code will return the date of the first specified day of a month.
Example:
To find the first Monday in May
FrstDay("May 2003",2) = 05/05/2003
To find first Monday of current month
FrstDay(Date(),2)

Adding 7 to the result will give second Monday, 14 will give third, etc..

Function FrstDay(D As Variant, ReqWeekday As Integer) As Date
  ' Returns the date of the first specified day in a month
  ' ReqWeekDay 1 - Sunday, 2 - Monday, 3 - Tuesday, etc.
  
  FrstDay = NextDay(EndOfMonthPrev(D), ReqWeekday)
    
End Function

Function NextDay(D As Variant, DayCode As Integer) As Variant
'
' Returns the date of the next DayCode (1=Sun ... 7=Sat) after the
' date D.

  NextDay = D - WeekDay(D) + DayCode + IIf(WeekDay(D) < DayCode, 0, 7)
End Function

 

[Jacob Mathai]

I tried Antomack's function. I received a compile error on 'endofmonthprev'. Sub or function not defined.
Is it a reference problem? Pls let me know how to fix it.

 

[antomack]

Apologies but I had left out one of the functions and am only after seeing your reply now. You need the function below as well as the other 2 given earlier.

Function EndOfMonthPrev(D As Variant) As Variant
  ' Returns the date representing the last day of the previous month.
  ' Arguments:
  ' D = Date
  
  EndOfMonthPrev = DateSerial(Year(D), Month(D), 0)
  
End Function