Blue Monday (1 Viewer)

D

DewMan

Registered User.

Local time

Today, 19:48

Joined

May 16, 2003

Messages

15

• Jul 11, 2003

• #1

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

Registered User.

Local time

Tomorrow, 04:48

Joined

Aug 9, 2001

Messages

461

• Jul 11, 2003

• #2

Rank but functional

On_Open of splash / main menu etc

Code:Copy to clipboard

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.

 

D

DewMan

Registered User.

Local time

Today, 19:48

Joined

May 16, 2003

Messages

15

• Jul 11, 2003

• #3

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!!!

 

R

Rich

Guest

• Jul 11, 2003

• #4

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

 

D

DewMan

Registered User.

Local time

Today, 19:48

Joined

May 16, 2003

Messages

15

• Jul 11, 2003

• #5

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

Registered User.

Local time

Tomorrow, 04:48

Joined

Aug 9, 2001

Messages

461

• Jul 11, 2003

• #6

We have created a monster

 

D

Dgar007

Guest

• Jul 11, 2003

• #7

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,

 

A

antomack

Registered User.

Local time

Today, 19:48

Joined

Jan 31, 2002

Messages

215

• Jul 14, 2003

• #8

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..

Code:Copy to clipboard

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

 

J

Jacob Mathai

Registered User.

Local time

Today, 19:48

Joined

Sep 6, 2001

Messages

546

• Jul 16, 2003

• #9

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.

 

A

antomack

Registered User.

Local time

Today, 19:48

Joined

Jan 31, 2002

Messages

215

• Jul 24, 2003

• #10

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.

Code:Copy to clipboard

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