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
ES-ComponentsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Create a criteria that looks something like"

WHERE [ShipDate] >= DateAdd("d", Weekday(Date(), 3), Date())
AND [ShipDate] < DateAdd("d", 5-weekday(Date(), 3), Date())
0
Gustav BrockCIOCommented:
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
0
ES-ComponentsAuthor Commented:
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())
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale FyeCommented:
Is your data in Access or some other database?

Are you trying to run this query from within Access or from Excel?
0
ES-ComponentsAuthor Commented:
My Data is in an Access 2007 Table.

Rick
0
Dale FyeCommented:
That should read:

SELECT *
FROM OpenOrdersFinal
WHERE [ShipDate] >= DateAdd("d", -Weekday(Date(), 3), Date())
AND [ShipDate] < DateAdd("d", 5-weekday(Date(), 3), Date())

There should be a minus sign before the "Weekday" in the first line of the criteria.  I don't know why you are getting that error though, because this modification works when I imported your spreadsheet into my database.

The error you are getting makes me think that you are trying to run this query from within EXCEL, is that the case?  Otherwise, I don't know why you would be getting that ODBC error.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.