Link to home
Start Free TrialLog in
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
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Create a criteria that looks something like"

WHERE [ShipDate] >= DateAdd("d", Weekday(Date(), 3), Date())
AND [ShipDate] < DateAdd("d", 5-weekday(Date(), 3), Date())
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
Avatar of ES-Components

ASKER

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())
Is your data in Access or some other database?

Are you trying to run this query from within Access or from Excel?
My Data is in an Access 2007 Table.

Rick
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial