Avatar of ES-Components
ES-ComponentsFlag for United States of America asked on

Select a Weekly Date Range In Access 2007 Based On The Current Day

I have a Customer Order Table with ShipDates. My Operations Mgr wants to be able to click a button and see what is supposed to ship this week. (Monday Through Friday)
See attached table as an example of what I have. I have put it on a excel spreadsheet for convenience.

Is there an Access 2007 Select statement or formula that I could use based on the Current Day to select out the "Current" Monday through Fridays orders? I would like to be able to do this regardless of what Month it is. Is this possible?

Thank you...
Rick
WeeklyDates.xls
Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

Create a criteria that looks something like"

WHERE [ShipDate] >= DateAdd("d", Weekday(Date(), 3), Date())
AND [ShipDate] < DateAdd("d", 5-weekday(Date(), 3), Date())
Gustav Brock

Or, if you need this on a wider scale, you could use these functions:
Public Function DateWeekFirst( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek) _
  As Date

' Returns the first date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.
' 2003-05-01. System settings used as default.
' 2012-10-44. Data type of lngFirstDayOfWeek changed to VbDayOfWeek.
    
  DateWeekFirst = DateAdd("d", vbSunday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
    
End Function

Public Function DateWeekLast( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = vbUseSystemDayOfWeek) _
  As Date

' Returns the last date of the week of datDate.
' lngFirstDayOfWeek defines the first weekday of the week.
' 2000-09-07. Cactus Data ApS.
' 2003-05-01. System settings used as default.
' 2012-10-44. Data type of lngFirstDayOfWeek changed to VbDayOfWeek.
    
  DateWeekLast = DateAdd("d", vbSaturday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
    
End Function

Open in new window

/gustav
ASKER
ES-Components

I tried your select statement:  I got this error (ODBC Microsoft Access Driver), Too Few parameters, Expected 1.
Any ideas as to how to fix?

thank you...
Rick

SELECT *
FROM OpenOrdersFinal
WHERE [ShipDate] >= DateAdd("d", Weekday(Date(), 3), Date())
AND [ShipDate] < DateAdd("d", 5-weekday(Date(), 3), Date())
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

Is your data in Access or some other database?

Are you trying to run this query from within Access or from Excel?
ASKER
ES-Components

My Data is in an Access 2007 Table.

Rick
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question