Find out if a specific date is in the current week in Access query

Hi all.

How can I find out if a specific date is in the current week in an Access query? Basically, I'm working on a query that will display all sales orders except those whose [salesorderdate] is in the current week.

Is there a specific function for this?

Thank you in advance.
Who is Participating?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:
You can easily calculate the current week's date range using:

The first day of the current week (assuming Sunday = day 1):
Date() - WeekDay(Date()) + 1
The last day of the current week:
Date() - WeekDay(Date()) + 7

If you want everything before the current week then use:

Where [salesorderdate] < (Date() - WeekDay(Date()) + 1)

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
You can use a query like this to show days in the current week:

SELECT Yourtable.YourDate
FROM Yourtable
WHERE ((([YourTable.YourDate]) Between DateAdd("d",-((Weekday(Date())-1)),Date()) And Date()));

There may be something a bit more elegant, but this will work
Jeffrey CoachmanMIS LiasonCommented:
<Is there a specific function for this?>
no, but you can make one

Here is a function based on the same expression
Public Function IsInCurrentWeek(YourDate As Date) As Boolean
    If YourDate >= DateAdd("d", -(Weekday(Date) - 1), Date) And YourDate <= Date Then
        IsInCurrentWeek = True
        IsInCurrentWeek = False
    End If
End Function

...Put this in a module, then call it in a query like this:

SELECT Yourtable.YourDate, IsInCurrentWeek([YourDate]) AS [Is In Current Week]
FROM Yourtable;

Returns True(-1) if the date IS in the current week, else it returns False.

Again, another expert may have something a bit more elegant, ...but this seems to work
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.

All Courses

From novice to tech pro — start learning today.