Share via

Query in MS Access Design view to include data only from previous week (Sun - Sat)

Anonymous
2020-04-07T19:07:16+00:00

Hi ,

Can I please know how to include the data for the date range of the previous week (Sun - Sat) in the Design view Criteria beneath.

Example : for every time i run the report it should give me the previous week's data (Sun - Sat) .

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.4K Reputation points MVP Volunteer Moderator
    2020-04-07T19:18:17+00:00

    Set the Criteria row to

    Between Date()-Weekday(Date())-6 And Date()-Weekday(Date())

    0 comments
  2. Tom van Stiphout 40,091 Reputation points MVP Volunteer Moderator
    2020-04-07T19:19:50+00:00

    Put this in a Standard Module:

    Public Function FirstDayInWeek(ByVal dt As Date) As Date

        Const FIRST_DAY_OF_WEEK = vbSunday

        Dim intDayNum   As Integer

        intDayNum = DatePart("w", dt, FIRST_DAY_OF_WEEK)

        dt = DateAdd("d", -intDayNum + 1, dt)

        FirstDayInWeek = dt

    End Function

    With that in place, you can do things like:

    select DateAccept, FirstDayInWeek(DateAccept)

    from vew_TEKStartsOffice

    Once you have that, you can use DateAdd to subtract 6 more days:

    select DateAccept, FirstDayInWeek(DateAccept), DateAdd('d', -6, FirstDayInWeek(DateAccept))

    from vew_TEKStartsOffice

    0 comments