Between Dates Query

[gmazza76]

Good afternoon

I have a formula in my query that identifies if today is Tuesday and sets the dates correctly for a between Monday and Friday of the previous week.
This works fine, but how would I add in to the below say the same for each day of the week?

Is this something that is possible?

Between IIf(Format(Date(),"ddd")="Tue",Date()-8) And IIf(Format(Date(),"ddd")="Tue",Date()-3)

thanks in advance

 

[gmazza76]

I have tried the following, but get an "You did not enter the keyword And in the Between ..... And Operator"

IIf(Format(Date(),"ddd")="Mon", Between Date()-7) And Date()-1),
IIf(Format(Date(),"ddd")="Tues", Between Date()-8) And Date()-2),
IIf(Format(Date(),"ddd")="Weds", Between Date()-9) And Date()-3),
IIf(Format(Date(),"ddd")="Thurs", Between Date()-10) And Date()-4),
Between Date()-11) And Date()-5))

Any pointers

 

[Joe4]

Do you ALWAYS want between Monday and Friday of the previous week, regardless of what day of the week it is this week?

I think you should be able to create a simple function without any Nested Ifs by incorporating the Weekday function.
Just apply it to the current date. See: Weekday Function - Microsoft Support

 

[gmazza76]

@Joe4 that would be ideal.

 

[Micron]

In case you need a suggestion on how to use that function (because I don't see a sample there that gives you what you want):
Your query might work if you use an expression like the following to get the date of Monday in the prior week:
Date() - Weekday(Date(), 2) + 1
then add 4 to that result as the second date. However, if your dates come from table fields then there are 2 issues I see with that.
1) the date the calculation will be based on isn't today
2) if the date is null you'll either return Null or the query will bomb; not sure which if not both. In either case, a function would be better as @Joe says because it can handle null as well as variable date inputs. For example, the function could return Monday date based on a date such as:
#09/29/2023# - Weekday(#09/29/2023#, vbMonday) + 1 which should return 9/25/23

There is also another pitfall that occurs when your date field values contain time (even if you don't see it, it is there as either 00:00:00 by default, or your format is hiding the time component). In that case, BETWEEN stops at midnight of the latter date, which means you get no records for the last day. By that I mean anything that gets time stamped at say 01:00:00 AM is not included. To avoid that I've used DateAdd to bring the time portion up to the last minute or second of the latter date.