Solved

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

Posted on 2013-05-20
3
548 Views
Last Modified: 2013-05-20
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.
0
Comment
Question by:printmedia
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 39181842
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

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39181880
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39181920
<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
    Else
        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
Database109.mdb
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

679 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