Query showing only this week days

[FL0XN0X]

Dear,

I have got a query which shows the upcoming 7 days, build using the crtiteria:



Question
1) How to adjust the query criteria to show this week days, starting on Monday till Friday ? (thus when I run the query on whatever day of the week, it should only show the currents week days)
2) How to adjust the query criteria to show next week days, starting on next week Monday till next week Friday ? (thus when I run the query on whatever day of the week, it should only show the week days of next week)

Thanks in advance.
Kind regards,


Bart

[June7]

Calculate the Monday of the week Datum is in and the Monday of the week Date() is in.

Like:

SELECT * FROM table WHERE 2-Weekday(Datum)+Datum = 2-Weekday(Date())+Date();

Next Week:

SELECT * FROM table WHERE 2-Weekday(Datum)+Datum = 2-Weekday(Date())+Date()+7;

[FL0XN0X]

only one line of code, but I do not understand the equation :-(.

2-Weekday(Datum) is an integer but then you add Datum .... I do not understand. Can you please explain ?

[FL0XN0X]

Hey June7,

I found this solution which I DO understand :-)

WHERE ((DatePart("ww",Kalender.Datum,2)=DatePart("ww",Da te(),2))) AND Year(Kalender.Datum)=year(Date())

Anyhow, I'd like to understand what you've come up with .....Can yo uplease elaborate a bit?

[June7]

I found it. Bing: Access calculate Monday current week

I have seen more than one version of the formula.

Your DatePart() might not provide what you want for the beginning and ending of the year. You said you want a Mon-Fri period. For instance, 12/31/2018 is on Monday and it is in week 53. Whereas 1/1/2019 will return as week 1. Should those two days actually capture in the same week?

Can see this in the VBA immediate window:

?DatePart("ww",#12/31/2018#)
53
?DatePart("ww",#1/1/2019#)
1