Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Solved
Last Monday of October
Posted on 2016-08-03
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(Yea
IIf(Weekday(DateSerial(Yea
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(Yea
IIf(Weekday(DateSerial(Yea
[X]
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
2
2-
2
- +3
9 Comments
Active today
I have a table of numbers (tbl_Numbers) with a single field (lngNumber) and records from 0 to 99.
SELECT Max(DateSerial(Year(Date()
FROM qry_Numbers
WHERE (Weekday(DateSerial(Year(D
AND (qry_Numbers.lngNumber Between 1 And 31);
SELECT Max(DateSerial(Year(Date()
FROM qry_Numbers
WHERE (Weekday(DateSerial(Year(D
AND (qry_Numbers.lngNumber Between 1 And 31);
Active today
Are you talking about a critieria in a query?
SELECT * FROM yourTable
WHERE [DateField] >= (
SELECT Max(DateSerial(Year(Date()
FROM qry_Numbers
WHERE (Weekday(DateSerial(Year(D
AND (qry_Numbers.lngNumber Between 1 And 31)
)
SELECT * FROM yourTable
WHERE [DateField] >= (
SELECT Max(DateSerial(Year(Date()
FROM qry_Numbers
WHERE (Weekday(DateSerial(Year(D
AND (qry_Numbers.lngNumber Between 1 And 31)
)
Active today
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
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
Active 1 day ago
HI,
pls try
pls try
=DateSerial(Year(Date())-1,11,1)-Weekday(DateSerial(Year(Date())-1,11,8-2))
Active today
You can use this generic function for finding a weekday of a month:
>= DateWeekdayInMonth(DateSer
or in a query:
>=DateWeekdayInMonth(DateS
/gustav
' 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
>= DateWeekdayInMonth(DateSer
or in a query:
>=DateWeekdayInMonth(DateS
/gustav
Active 1 day ago
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.
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.
Active 1 day ago
You can also try to make it into a generic function and use it with any date you like:
CREATE FUNCTION
IsLastMondayOfMonth(@dateT
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
CREATE FUNCTION
IsLastMondayOfMonth(@dateT
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
Featured Post
Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database. This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month10 days, 9 hours left to enroll
618 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.