First Monday of the Month 2

[r0nniem]

From thread705-664946

Apparently this is for the first Saturday of the next month.
DateSerial(Year(mydte), Month(mydte) + 1, 1 + (7 - Weekday(DateSerial(Year(mydte), Month(mydte) + 1, 1))))

How can I make this work for the First Monday of the current month?

Thanks
Veronica

 

[jrbarnett]

Replace all occurrences of mydte with date()

John

 

[PHV]

A starting point:
Public Function FirstMonday(myDate As Date) As Date
Dim d As Date, w As Integer
d = DateSerial(Year(myDate), Month(myDate), 1)
w = Weekday(d, vbMonday)
FirstMonday = d + IIf(w, 8 - w, 0)
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PWise]

create a table [Digits] with numbers [digitid] from 1 to 31
run this query

Select min(dt.firstmonday)  AS FirstMonday from
(SELECT DateAdd("d",[digitid],Date()) AS FirstMonday
FROM Digits
WHERE (((Digits.DigitID)<32)) and weekday(DateAdd("d",[digitid],Date()) )=2 and month(DateAdd("d",[digitid],Date()) )<>month(date())) as dt

 

[traingamer]

One way:

DateSerial(Year(mydte), Month(mydte), ((7 - Weekday(DateSerial(Year(mydte), Month(mydte), 7))) DateSerial(Year(#10/6/2006#), Month(#10/6/2006#), ((7 - Weekday(DateSerial(Year(#10/6/2006#), Month(#10/6/2006#), 7))) + 2) mod 7))

You can replace mydte with Date() to get the first monday of this month or use any valid date as mydte. This function determines the day of week for the seventh (7th) day of the month in question, then subtracts an appropriate number of days (without backing into the previous month).
i.e. + 2) mod 7)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Sorry - tested

DateSerial(Year(mydte), Month(mydte), ((7 - Weekday(DateSerial(Year(mydte), Month(mydte), 7))) + 2) mod 7)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PWise]

Sorry did not notice that you need first monday for this month use this query

Select min(dt.firstmonday)  AS FirstMonday from
(SELECT DateAdd("d",-[digitid],Date()) AS FirstMonday
FROM Digits
WHERE weekday(DateAdd("d",-[digitid],Date()) )=2 and month(DateAdd("d",-[digitid],Date()) )=month(date())) as dt

 

[r0nniem]

Thank you guys for your assistance, you saved my brain from going musshy ... or mushier ... it's all working fabulous now