×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

currentWeek excluding weekends

currentWeek excluding weekends

currentWeek excluding weekends

(OP)
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.

RE: currentWeek excluding weekends

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

CODE

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 

RE: currentWeek excluding weekends

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

---- Andy

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

RE: currentWeek excluding weekends

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.

RE: currentWeek excluding weekends

(OP)
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!

RE: currentWeek excluding weekends

>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:

CODE

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:

CODE

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

RE: currentWeek excluding weekends

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)

CODE -->

' Returns date of specified day of the week (default is Monday) of the date passed. Assumes week runs from Sunday to Saturday
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 

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

CODE -->

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close