Solved

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

Posted on 2013-05-20
3
523 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now