## Days / Week Ending Date for a Month

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

### Days / Week Ending Date for a Month

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

HansV
Posts: 71153
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Days / Week Ending Date for a Month

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.
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

### Re: Days / Week Ending Date for a Month

Thanks Hans! I appreciate it!