Is it possible to filter to current or next Fri. when opened?

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Is it possible to filter to current or next Fri. when opened?

Post by bknight »

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?

User avatar
HansV
Administrator
Posts: 78238
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?

Post by HansV »

Set the Criteria for the date field to

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
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Is it possible to filter to current or next Fri. when opened?

Post by bknight »

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.

User avatar
HansV
Administrator
Posts: 78238
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?

Post by HansV »

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") & "*"
Last edited by HansV on 24 Jul 2020, 19:34, edited 1 time in total.
Reason: to correct use of Weekday
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Is it possible to filter to current or next Fri. when opened?

Post by bknight »

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.

User avatar
HansV
Administrator
Posts: 78238
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?

Post by HansV »

Ah sorry, my mistake. It should have been Weekday(Date(), 7):

Like "*" & Format(Date()+7-Weekday(Date(),7),"yymmdd") & "*"
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Is it possible to filter to current or next Fri. when opened?

Post by bknight »

Wonderful !!!! :cheers: :clapping: