Current Monday as an anchor in a query

[lonesoac0]

hello all,

I am trying to create a function for a SELECT query that uses the current weeks Monday and goes to Sunday. Then the following Monday the records are reset. For example last weeks data is,

Monday 26 Sep data data data
Tuesday 27 Sep data data data
Wed 28 Sep data data data


Thurs 29 Sep data data data
Fri 30 Sep data data data
Sat 1 Oct data data data
Sun 2 Oct data data data

The above records are only presented for that date range of that specific week. This week the records should be so far on a Wednesday:

Monday 3 Oct data data data
Tuesday 4 Oct data data data

As you will note, my data is going to be always behind one day of the current day.

The current Where clause I have is:

WHERE (((SDTicketClosuresforyesterday.Dates) Between (DateAdd("d",-7,Date())) And (Date())));

This gets 7 days, but it does not do what I describe above.

I hope I have explained this well enough.

[aytee111]

You can use DatePart to get the days of the week (among other things). DatePart(Date()) will be a 4 (Wed), Monday will be a 2, so subtract 2 days from Date() to get Monday's date.

[orange]

What are you trying to do in plain English--no Access or SQL jargon?

Tell us how this fits into your business processes.

You can use the WeedayName and WeekDay function and the Date intrinsic to get the following:

?weekdayname(weekday(date)) & " " & Date
Wednesday 05-Oct-2016

[lonesoac0]

so, an update on this issue. I got really lucky last night and found a function that goes in the where clause that gets all values for the month. I just need to adapt it for the week now.

WHERE  (((DatePart("m",[Dates]))=DatePart("m",DateAdd("m",-1,Date()))) AND  ((DatePart("yyyy",[Dates]))=DatePart("yyyy",DateAdd("m",-1,Date()))));

[lonesoac0]

I think I have a winner. I want this where statement to get last week's information.

WHERE (((DatePart("ww",[Dates]))=DatePart("ww",DateAdd("ww",-1,Date())) And (DatePart("ww",[Dates]))=DatePart("ww",DateAdd("ww",-1,Date()))));

[aytee111]

"ww" is the week number, you want the week day number, which is "w"

To get Monday's date then:

IIf(Datepart("w",Date()) > 2, Date() - DatePart("w",Date()) + 2,??)

10/7 minus 6 (Friday) + 2 (Monday) = 10/3

Fix the question marks - what do you want to happen if today is a Sunday or a Monday


(Not sure what "Dates" is, not a very descriptive field name!)