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
Days / Week Ending Date for a Month
-
- Administrator
- Posts: 78574
- 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:
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.
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
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
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Days / Week Ending Date for a Month
Thanks Hans! I appreciate it!