Last Monday of October

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)=1,DateSerial(Year(Date())-1,10,31-6),
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")))))))
garyrobbinsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
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);
garyrobbinsAuthor Commented:
how would you use that as the criteria?
Dale FyeOwner, Dev-Soln LLCCommented:
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)
)
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Download eBook
PatHartmanCommented:
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)
    Else
        fDateDayPrevious = dtmDate + bytDaySought - WeekDay(dtmDate) - 7
    End If
End Function

Open in new window

UsefulDateFunctions160822WorksWith64.zip
Rgonzo1971Commented:
HI,

pls try

=DateSerial(Year(Date())-1,11,1)-Weekday(DateSerial(Year(Date())-1,11,8-2))

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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, _
            vbSunday
        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

Then:

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

or in a query:

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

/gustav
xtermieCommented:
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
select
 case
 when datepart(dw, GETDATE()) = 2 and DATEPART(month, DATEADD(day, 7, GETDATE())) <> DATEPART(month, GETDATE())
 then 1
 else 0
 end
---
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.
xtermieCommented:
You can also try to make it into a generic function and use it with any date you like:
CREATE FUNCTION
IsLastMondayOfMonth(@dateToCheck datetime)
RETURNS bit
AS
BEGIN
DECLARE
@result bit

SELECT @result =
    CASE  
       WHEN datepart(dw, @dateToCheck) = 2 AND DATEPART(month, DATEADD(day, 7, @dateToCheck)) <> DATEPART(month, @dateToCheck)
    THEN 1
 ELSE 0
 END
 RETURN @result
END
garyrobbinsAuthor Commented:
This is exactly what I was looking for, Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.