Help with a module

[hotplate]

Hi,
A few months ago I posted this question:

I am trying to make the default value the date of the first monday in
the month in an unbound box. Is there a way to do this?

I used the following solution by Arvin Meyer, but today I ran into a
problem. Since the first monday is the 5th and today is the 1st, I
need the first monday of last month instead. I know this only happens
a few days every month, but I thought there might be a way to fix
this. Could someone help me modify this module for this situation?

James
____________________________________________________

Paste the following function into a standard module, and save it:

Function FirstMonday(Mo As Variant, Yr As Variant)
'Arvin Meyer 9/25/2006
Dim FM As Variant
FM = 10 - WeekDay(Mo & "/1/" & Yr)
If FM > 7 Then FM = FM - 7
FirstMonday = DateValue(Mo & "/" & str$(FM) & "/" & Yr)
End Function

Set the DefaultValue of the textbox to:

= FirstMonday(Month(Date()), Year(Date()))

 

[Douglas J. Steele]

So are you saying you want 2007-02-05 as the value for March 1, 2, 3 & 4,
but 2007-03-05 for the rest of the month?

=IIf(FirstMonday(Month(Date()), Year(Date())) > Date(),
FirstMonday(Month(Date()) - 1, Year(Date())), FirstMonday(Month(Date()),
Year(Date())))

You'll need to change the function slightly:

Function FirstMonday(Mo As Variant, Yr As Variant)
'Arvin Meyer 9/25/2006
'Doug Steele 2007-03-01

Dim FM As Variant

FM = 10 - WeekDay(DateSerial(Yr, Mo, 1)
If FM > 7 Then FM = FM - 7
FirstMonday = DateSerial(Yr, Mo, FM)

End Function

 

[hotplate]

Thanks,
Worked great! Just in case anyone else wants to copy the module: (One
parenthesis was missing)

Function FirstMonday(Mo As Variant, Yr As Variant)
'Arvin Meyer 9/25/2006
'Doug Steele 2007-03-01

Dim FM As Variant

FM = 10 - WeekDay(DateSerial(Yr, Mo, 1))
If FM > 7 Then FM = FM - 7
FirstMonday = DateSerial(Yr, Mo, FM)

End Function

James