currentWeek excluding weekends

I want a query for the current week but for a particular day of that week.

So for instance I want to show all my records for a Monday for the current week. That would be tied to a report to show those records.

I would like a query like that for each day of the current week, excluding weekends, which would tied to a report to show those individual days of the current week

Your help is greatly appreciated in advance.

 

[jedraw]

Here are a few date calculations that may offer some insight.

Sub SomedateStuff()
10    Debug.Print "First Day of Week is Sunday"
20    Debug.Print "First day Last Week " & DateAdd("ww", -1, Date - Weekday(Date) + 1)
30    Debug.Print "Last day Last Week " & DateAdd("ww", -1, Date - Weekday(Date) + 7)
40    Debug.Print "We are in week " & Format(Date, "ww")
50    Debug.Print "Start of  16TH week in 2023 is " & DateAdd("d", ((16 - 1) * 7), #1/1/2023#)
60    Debug.Print "End of Second week in 2023 is " & DateAdd("d", (2 - 1) * 7, #1/1/2023#)
70    Debug.Print "Start of current week in 2023 is " & DateAdd("d", ((Format(Date, "ww") - 1) * 7), #1/1/2023#)
'Since start of week is Sunday, you can get Monday by adding 1 to the Start of Current week
'Tuesday by adding 2,  Wednesday by adding 3....
'for example 
80    Debug.Print "Monday current week is " & DateAdd("d", ((Format(Date, "ww") - 1) * 7), #1/1/2023#) + 1
End Sub

 

[Andrzejek]

Why not simply allow user to select 'a particular day of that week'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[sxschech]

My initial take is that probably so the query can be run without having to enter a date, if it is Tuesday the query will automatically select the correct day. Then again I reread it so maybe that isn't entirely correct and that the user still wants to select a particular day, in which case your approach makes more sense.

 

[caykamanj1966]

All,
I don't want the user to select a date.

So this is a weekly report that we review activities for the week.

So this is what I really want.

For instance, if all records have been entered for a Monday of the current week. When the user opens the report, he will see all the records for that Monday of the current week.

When the user opens the report on Tuesday, after records have been entered, he will see all the records for Monday for the current week and he will see the records for Tuesday for the current week.

When the user opens the report on Wednesday, after records have been entered, he will see all the records for Monday, Tuesday & Wednesday for the current week and so on until Friday.

The next week will be the same scenario.

This is what I want. Can you help me with the coding for this?

Thanks in advance!

 

[Andrzejek]

>For instance, if NO records have been entered for a Monday of the current week. When the user opens the report ON MONDAY, he will see NO records IN THE REPORT - correct?

Based on jedraw's post:

Option Explicit

Sub SomeDateStuff()
Dim datMonday As Date
Dim datFriday As Date
Dim strSQL As String

datMonday = DateAdd("d", ((Format(Date, "ww") - 1) * 7), CDate("1/1/" & Year(Date))) + 1
Debug.Print "Monday of the current week is " & datMonday
datFriday = datMonday + 4
Debug.Print "Friday of the current week is " & datFriday

strSQL = "Select some records from some table " & vbNewLine & _
    "Where some date field BETWEEN #" & datMonday & "# and #" & datFriday & "#)"
Debug.Print strSQL

End Sub

or, forget about Friday and do just:

strSQL = "Select some records from some table " & vbNewLine & _
    "Where some date field BETWEEN #" & datMonday & "# and #" & Date() & "#)"
Debug.Print strSQL

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Personally I'd turn this into a function (and use a slightly different method to those above, one in which I think it is somewhat clearer what is going on. IMHO)

[COLOR=blue][COLOR=green]' Returns date of specified day of the week (default is Monday) of the date passed. Assumes week runs from Sunday to Saturday[/color]
Public Function GetWeekdaydate(SourceDate As Date, Optional DayOfWeek As VbDayOfWeek = vbMonday) As Date
    GetWeekdaydate = DateSerial(Year(SourceDate), Month(SourceDate), Day(SourceDate) - Weekday(SourceDate) + DayOfWeek)
End Function[/color]

And then Andy's Select statement could be something like:

[COLOR=blue]strSQL = "Select some records from some table " & vbNewLine & _
    "Where some date field BETWEEN #" & GetWeekdaydate(Date) & "# and #" & Date() & "#)"
Debug.Print strSQL[/color]