Is it possible to filter to current or next Fri. when opened?
-
- BronzeLounger
- Posts: 1380
- Joined: 08 Jul 2016, 18:53
Is it possible to filter to current or next Fri. when opened?
I have a query that has date as one of the fields. Is it possible to put some statement such that when opened it the criteria searches for the next or current Friday? For example if I opened the query today it would find today's date, but if I opened it tomorrow then it would search for 31 Jul 2020?
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to filter to current or next Fri. when opened?
Set the Criteria for the date field to
Date()+7-Weekday(Date(),7)
Date()+7-Weekday(Date(),7)
Last edited by HansV on 24 Jul 2020, 19:34, edited 1 time in total.
Reason: to correct use of Weekday
Reason: to correct use of Weekday
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1380
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to filter to current or next Fri. when opened?
Ok is it possible to make that criteria into part of a string? The current criteria is
Like "*200724*" but of course I have to change it every week manually. I didn't make the situation as clear as I should have.
Like "*200724*" but of course I have to change it every week manually. I didn't make the situation as clear as I should have.
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to filter to current or next Fri. when opened?
Do you mean that the field is a text field?
And if so, does it contain other text than the date?
Try
Like "*" & Format(Date()+7-Weekday(Date(),7),"yymmdd") & "*"
And if so, does it contain other text than the date?
Try
Like "*" & Format(Date()+7-Weekday(Date(),7),"yymmdd") & "*"
Last edited by HansV on 24 Jul 2020, 19:34, edited 1 time in total.
Reason: to correct use of Weekday
Reason: to correct use of Weekday
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1380
- Joined: 08 Jul 2016, 18:53
Re: Is it possible to filter to current or next Fri. when opened?
Yes the filed is a text field with the date imbedded in each string.
AVGO 200724C327.5 is an example, sorry for the confusion.
Your suggestion yielded no results and there are two. The above is one of them the other is AVGO 200724C325.
Next weeks items are CVX 200731C93 and CVX 200731C94.
AVGO 200724C327.5 is an example, sorry for the confusion.
Your suggestion yielded no results and there are two. The above is one of them the other is AVGO 200724C325.
Next weeks items are CVX 200731C93 and CVX 200731C94.
-
- Administrator
- Posts: 78457
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Is it possible to filter to current or next Fri. when opened?
Ah sorry, my mistake. It should have been Weekday(Date(), 7):
Like "*" & Format(Date()+7-Weekday(Date(),7),"yymmdd") & "*"
Like "*" & Format(Date()+7-Weekday(Date(),7),"yymmdd") & "*"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1380
- Joined: 08 Jul 2016, 18:53