MS Access Need Help With Query Criteria and Weekday Function Showing Last Wednesday To This Last Tuesday

Dustin Stanley
Dustin Stanley used Ask the Experts™
on
I am needing some help with the Weekday function in a query criteria. I have never used it so I don't have much experience. I am working on a payroll system currently.

The idea here is to pay the employees on Fridays.  The paycheck will consist of the days Wednesday until Tuesday. Then the employee receives the check on that following Friday.

I am having trouble understanding how to put this together. I thank you for help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can find the previous Tuesday and Wednesday using this function:

' Returns the date of the weekday as specified by DayOfWeek
' before Date1.
'
'   2016-01-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DatePreviousWeekday( _
    ByVal Date1 As Date, _
    Optional ByVal DayOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek) _
    As Date

    Dim ResultDate  As Date
    
    If DayOfWeek = vbUseSystemDayOfWeek Then
        DayOfWeek = Weekday(Date1)
    End If
    
    ResultDate = DateAdd("d", 1 - Weekday(Date1, DayOfWeek), Date1)
    
    DatePreviousWeekday = ResultDate
    
End Function

Open in new window

So:

FromWednesday = DateAdd("w", -1, DatePreviousWeekday(Date, vbWednesday))
ToTuesday = DatePreviousWeekday(Date, vbTuesday)

Open in new window

Dustin StanleyEntrepreneur

Author

Commented:
Thank you Gustav. I will try this in the morning. Have a great night!
Dustin StanleyEntrepreneur

Author

Commented:
THANK YOU! This worked perfectly. I did have a small issue with the dates so I used in my query:

Between DateAdd("d",-7,DatePreviousWeekday(Date(),4)) And DatePreviousWeekday(Date(),4)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, the constants are not known in SQL.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial