Solved

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 5 54
Auto-generated ID's in MS Access 9 46
Delete Records on a Form in Microsoft Access 5 39
Access summarise unique values in tables 2 16
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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