Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Last Monday of October

Posted on 2016-08-03
9
80 Views
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)=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")))))))
0
Comment
Question by:garyrobbins
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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);
0
 

Author Comment

by:garyrobbins
ID: 41741308
how would you use that as the criteria?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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)
)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 36

Expert Comment

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

Open in new window

UsefulDateFunctions160822WorksWith64.zip
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41741807
HI,

pls try

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

Open in new window

Regards
0
 
LVL 49

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, _
            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
0
 
LVL 18

Expert Comment

by:xtermie
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
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.
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41741952
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
0
 

Author Closing Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

839 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