Days / Week Ending Date for a Month

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Days / Week Ending Date for a Month

Post by Leesha »

HI,
I have a query that counts the [TotalDays] a store has sales during a week ending date with Tuesday as the week ending date. The purpose of the query is to determine the number of days a store is closed during a [WKEndingDate] time frame.
I am able to calculate the number of days the store was open during a given [WKEndingDate] time frame, however am not sure how to set up the formula that would show the total days that are in the month/[WKEndingDate].

For example WE 4/6/21 = 6 days, 4/13/21 = 7 days, 4/20/21 = 7 days, 4/27/21 = 7 days and 5/4/21 = 3 days in April.
The sql to get the total days open is:

SELECT qryHWSalesLog1PartialInitial.Store_ID, qryHWSalesLog1PartialInitial.WKEndingDate, Count(qryHWSalesLog1PartialInitial.WKEndingDate) AS TotalDays
FROM qryHWSalesLog1PartialInitial
GROUP BY qryHWSalesLog1PartialInitial.Store_ID, qryHWSalesLog1PartialInitial.WKEndingDate
ORDER BY qryHWSalesLog1PartialInitial.WKEndingDate;

Thanks!
Leesha

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Days / Week Ending Date for a Month

Post by HansV »

I'd use a custom VBA function:

Code: Select all

Function DaysInWeek(WeekEndingDate As Date) As Long
    Dim e As Long
    Dim d As Long
    e = Weekday(WeekEndingDate, vbTuesday)
    If e = 1 Then
        d = Day(WeekEndingDate)
        If d <= 6 Then
            DaysInWeek = d
        Else
            DaysInWeek = 7
        End If
    Else
        DaysInWeek = e - 1
    End If
End Function
Use like this:

DaysWeek: DaysInWeek([WKEndingDate])

You can add this either to qryHWSalesLog1PartialInitial or to the totals query in your post. If you do the latter, you'll have to Group By the new column.
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Days / Week Ending Date for a Month

Post by Leesha »

Thanks Hans! I appreciate it!