Last Monday of October

Posted on 2016-08-03
Medium Priority
Last Modified: 2016-08-05
Hello All

I am curious to see if there is a cleaner way to query by the last monday in october of the previous year.  I have the below and it works but i am sure there is a cleaner way to do it that i am not thinking of.

IIf(Weekday(DateSerial(Year(Date())-1,10,31),1)=7,DateSerial(Year(Date())-1,10,31-5), IIf(Weekday(DateSerial(Year(Date())-1,10,31),1)=6,DateSerial(Year(Date())-1,10,31-4), IIf(Weekday(DateSerial(Year(Date())-1,10,31),1)=5,DateSerial(Year(Date())-1,10,31-3), IIf(Weekday(DateSerial(Year(Date())-1,10,31),1)=4,DateSerial(Year(Date())-1,10,31-2), IIf(Weekday(DateSerial(Year(Date())-1,10,31),1)=3,DateSerial(Year(Date())-1,10,31-1), IIf(Weekday(DateSerial(Year(Date())-1,10,31),1)=2,DateSerial(Year(Date())-1,10,31),"ERROR")))))))
Question by:garyrobbins
  • 2
  • 2
  • 2
  • +3
LVL 50

Expert Comment

by:Dale Fye
ID: 41741291
I have a table of numbers (tbl_Numbers) with a single field (lngNumber) and records from 0 to 99.

SELECT Max(DateSerial(Year(Date())-1,10,[lngNumber])) AS LastMonday
FROM qry_Numbers
WHERE (Weekday(DateSerial(Year(Date())-1,10,[lngNumber]),1)=1)
AND (qry_Numbers.lngNumber Between 1 And 31);

Author Comment

ID: 41741308
how would you use that as the criteria?
LVL 50

Expert Comment

by:Dale Fye
ID: 41741319
Are you talking about a critieria in a query?

SELECT * FROM yourTable
WHERE [DateField] >= (
SELECT Max(DateSerial(Year(Date())-1,10,[lngNumber])) AS LastMonday
FROM qry_Numbers
WHERE (Weekday(DateSerial(Year(Date())-1,10,[lngNumber]),1)=1)
AND (qry_Numbers.lngNumber Between 1 And 31)
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

LVL 41

Expert Comment

ID: 41741415
I've attached a database full of date functions.  Look at the second tab to see this one in action.
Here is a function that will do it:
Pass in November 1st and a 2 for Monday
Function fDateDayPrevious(dtmDate As Date, bytDaySought As Byte) As Date
'   Function to return the date of the named day previous to the passed date
'   Accepts:bytDaySought (e.g.: vbSunday)
'   dtmDate as date
    If bytDaySought - WeekDay(dtmDate) <= 0 Then
        fDateDayPrevious = dtmDate + bytDaySought - WeekDay(dtmDate)
        fDateDayPrevious = dtmDate + bytDaySought - WeekDay(dtmDate) - 7
    End If
End Function

Open in new window

LVL 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 41741807

pls try


Open in new window

LVL 53

Expert Comment

by:Gustav Brock
ID: 41741882
You can use this generic function for finding a weekday of a month:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
' 2016-06-10. Gustav Brock, Cactus Data ApS, CPH.
Public Function DateWeekdayInMonth( _
    ByVal DateInMonth As Date, _
    Optional ByVal Occurrence As Integer, _
    Optional ByVal Weekday As VbDayOfWeek = -1) _
    As Date
    Const DaysPerWeek   As Long = 7
    Dim Offset      As Integer
    Dim Month       As Integer
    Dim Year        As Integer
    Dim ResultDate  As Date
    ' Validate Weekday.
    Select Case Weekday
        Case _
            vbMonday, _
            vbTuesday, _
            vbWednesday, _
            vbThursday, _
            vbFriday, _
            vbSaturday, _
        Case Else
            ' Zero, none or invalid value for VbDayOfWeek.
            Weekday = VBA.Weekday(DateInMonth)
    End Select
    ' Validate Occurrence.
    If Occurrence <= 0 Then
        Occurrence = 1
    ElseIf Occurrence > 5 Then
        Occurrence = 5
    End If
    ' Start date.
    Month = VBA.Month(DateInMonth)
    Year = VBA.Year(DateInMonth)
    ResultDate = DateSerial(Year, Month, 1)
    ' Find offset of Weekday from first day of month.
    Offset = DaysPerWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysPerWeek) Mod DaysPerWeek
    ' Calculate result date.
    ResultDate = DateAdd("d", Offset, ResultDate)
    If Occurrence = 5 Then
        ' The latest occurrency of Weekday is requested.
        ' Check if there really is a fifth occurrence of Weekday in this month.
        If VBA.Month(ResultDate) <> Month Then
            ' There are only four occurrencies of Weekday in this month.
            ' Return the fourth as the latest.
            ResultDate = DateAdd("d", -DaysPerWeek, ResultDate)
        End If
    End If
    DateWeekdayInMonth = ResultDate
End Function

Open in new window


    >= DateWeekdayInMonth(DateSerial(Year(Date) - 1, 10, 1), 5, vbMonday)

or in a query:


LVL 18

Expert Comment

ID: 41741949
Have something similar, which checks if the date is the last monday of the month using T-SQL.
The following select will return 1 if the current date is the last monday of the month, and 0 if not
 when datepart(dw, GETDATE()) = 2 and DATEPART(month, DATEADD(day, 7, GETDATE())) <> DATEPART(month, GETDATE())
 then 1
 else 0
datepart(dw, GETDATE()) returns the day of the week. Monday is 2. The second part adds 7 days to the current date and checks that within 7 days the month has changed (if it does not, it is not the last monday).  Change the GETDATE()'s to any date you want to check.
LVL 18

Expert Comment

ID: 41741952
You can also try to make it into a generic function and use it with any date you like:
IsLastMondayOfMonth(@dateToCheck datetime)
@result bit

SELECT @result =
       WHEN datepart(dw, @dateToCheck) = 2 AND DATEPART(month, DATEADD(day, 7, @dateToCheck)) <> DATEPART(month, @dateToCheck)
    THEN 1
 RETURN @result

Author Closing Comment

ID: 41744340
This is exactly what I was looking for, Thank you.

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question