Date Criteria clarification

[djclinton15]

I understand how in Access when one wants to create a “weekly” query whether it’s the previous week, the current week, or the next week query will return starting on Sunday and ending on Saturday. However I would like my weekday’s query to start from Monday and end on Sunday. Might anyone here know/tell me how I can create a ‘current weekday’ query & a ‘next week’ query?



I tried these 2-criteria’ which seemed to work, but don’t know if they are correct & will work in the long run:
To have a current weekday query staring from Sun to Sat criteria be: Between Date()-3 and Date()+4
To have a next week query staring from Sun to Sat criteria be: Between Date()+7 and Date()+13

Please, any advice/suggestion would be greatly appreciated! Thanks in advance!

[pbaldy]

Those will only work if run on a particular day of the week, as both are relative to the current date. See if this helps:

https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

[djclinton15]

Thanks pbaldy, couldn't find what I needed.

[pbaldy]

Really? How about here?

https://www.devhut.net/2010/06/22/ms...y-of-the-week/

[ssanfu]

weekday’s query to start from Monday and end on Sunday

I came up with these two functions to calculate dates for start date on Monday and end date on Sunday:

Public Function fWeekStartMonday(TheDate As Date) As Date
    Dim NumOfDays As Integer

    NumOfDays = Weekday(TheDate) - vbMonday

    'return start of week date
    fWeekStartMonday = DateAdd("d", -NumOfDays, TheDate)

End Function

'---------------------------------------------

Public Function fWeekEndSunday(TheDate As Date) As Date
    Dim NumOfDays As Integer

    NumOfDays = Weekday(TheDate) - vbMonday

    'return end of week date
    fWeekEndSunday = DateAdd("d", 6, DateAdd("d", -(NumOfDays), TheDate))

End Function

Examples:
-------------
(specific date)
Between fWeekStartMonday(#12/5/2017#) and fWeekEndSunday(#12/5/2017#)
or
(last week)
Between fWeekStartMonday(Date()-7) and fWeekEndSunday(Date()-7)
or
(this week)
Between fWeekStartMonday(Date()) and fWeekEndSunday(Date())
or
(next week)
Between fWeekStartMonday(Date()+7) and fWeekEndSunday(Date()+7)

[ArviLaanemets]

WEEKDAY(TheDate,1) returns 1 when date is Monday, and 7 when date is Sunday.

I.e.

= TheDate - (Weekday(TheDate,1) -1)

returns Monday of ISO week, the TheDate belongs to.

More difficult will be the calculating ISO week number. ISO week starts at Monday, and the week belongs to year, it's Thursday belongs into. I.e when 1st January is on Friday, Saturday or Sunday, then these are days of last week of previous year. And p.e. when 1st January is Thursday, then December 29., 30., and 31. belongs to 1st week of new year.

Edit: when driving home after work, it occurred me, that I erred on formula parameter! There must be everywhere
WEEKDAY(TheDate,2)

[djclinton15]

Frist, thanks for replying to my thread. The examples you gave, (this week) & (next week) I'm not sure where to placed them into the VBA code you provided above?/ I'm not well-versed with VBA Coding at all!

[ssanfu]

Put the 2 functions (Function fWeekStartMonday and Function fWeekEndSunday) in a standard module.

[Thompyt]

What about Between Date() and Date()+6.

[pbaldy]

What about Between Date() and Date()+6.

That doesn't account for Sun-Sat.

[djclinton15]

Steve, I tried your suggestion:

(Function fWeekStartMonday and Function fWeekEndSunday) in a standard module. I received this error, Compile error: in query expression 'Format([EmplDate<"yyyy". which doesn't make sense. For my "Years" field in my query I have it written as, Yrs: Format([EmplDate],"yyyy"

All of the other Codes that were suggested from other members were VBA Codes, right?

Again, I thank everyone here for your suggestions, but I might be over my head & am very frustrated at nothing working! I don't know if I need more in-depth instructions or chalk this one up as being out of my range.

[pbaldy]

A no VBA solution if you want to always base on the current date:

Between Date() - Weekday(Date()) + 1 And Date() + (7 - Weekday(Date()))

?date() - Weekday(date()) + 1
12/31/2017
?date() + (7 - Weekday(date()))
1/6/2018

[djclinton15]

[QUOTE=pbaldy;382712]A no VBA solution if you want to always base on the current date:

Between Date() - Weekday(Date()) + 1 And Date() + (7 - Weekday(Date()))

pbaldy, I'm assuming this criteria applies to the current week? Would this criteria apply for next week:

Between Date() - Weekday(Date()) + 8 And Date() + (14- Weekday(Date()))

[pbaldy]

Did you test?

?date() - Weekday(date()) + 8
1/7/2018
?date() + (14 - Weekday(date()))
1/13/2018

[djclinton15]

Not too sure what you mean by 'test,' however I do have a Table (actually it's query based on this year '2018') I'm using the formula with dates stating from 1/1/2018 to 3/31/2018. When I ran my 'NxtWk' query with the formula, Between Date() - Weekday(Date()) +

8

And Date() + (

1

4

- Weekday(Date()))

it came back with the dates 1/7/2017 (Mon) to 1/13/2013 (Sat) next week's dates. So, I'm hopefully assuming it's working?