Link to home
Start Free TrialLog in
Avatar of Senniger1
Senniger1

asked on

Filter on First Day of Week

I have the following code which was given to me by someone at Experts exchange which I use to filter.  

Public Function DateWeekFirst( _
  ByVal datDate As Date, _
  Optional ByVal lngFirstDayOfWeek As Long = 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.
   
  ' No special error handling.
  On Error Resume Next
 
  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbUseSystemDayOfWeek
  End Select
 
  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.
   
  ' No special error handling.
  On Error Resume Next
 
  ' Validate lngFirstDayOfWeek.
  Select Case lngFirstDayOfWeek
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday, _
      vbUseSystemDayOfWeek
    Case Else
      lngFirstDayOfWeek = vbUseSystemDayOfWeek
  End Select
     
  DateWeekLast = DateAdd("d", vbSaturday - WeekDay(datDate, lngFirstDayOfWeek), datDate)
   
End Function

I use this code in a query to filter the beginning of a week and a date range of one week as shown below:

LastWeekDateWeekFirst = DateAdd("d", -7, [DateWeekLast])

The problem is the query is returning a date range of Sunday through Monday and I need it to show a date range of Monday through Sunday because that is what we consider one week.

Can anyone help me alter this code.

Thanks in advance.
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation 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
Avatar of Senniger1
Senniger1

ASKER

Okay now I understand.  Since I didn't write the code, I wasn't 100% sure how to fix and my attempts didn't work out.

You gave me exactly what I needed.  

Thank you very much!
Between DateWeekFirst(Date()) And DateWeekLast(Date())