Query last week Mon-Sun

[arcaidius]

Hello,

I am running a query to see records from last week, but I need the week to be Mon-Sun.

I have this in the criteria, but for some reason it is not showing records for Sun at all, I changed the date of the record to last Sun and the previous Sun and it just does not show up. For some reason it is showing records for Mon-Sat of the previous week. I have this in the Criteria field for the date.

Between (Date()-7-Weekday(Date(),2)+1) And (Date()-Weekday(Date(),2))

Can you please help me get this to show records for Mon-Sun of the previous week?

 

[Joe4]

Your code returns dates between 12/6/2021 and 12/12/2021.
That should work provided that the field you are applying this to does NOT have a time component.
Does the field that you are applying this to have a date component?

If it does have a time component, you will probably miss most entries on 12/12.
That is because if you have a date with no time component, it assume 12:00 AM (midnight).
So any time on that date would be excluded, as it is after midnight.

 

[arcaidius]

Thanks, this makes sense. so the format in the table is short date, so you don't see any time, but when I highlight the date in the field there is a time there (9:05 am for the record I am testing it on). The input form field is a text box with a date picker, but the date is put in mostly with =Now() code after update from another field. This could be why there is a time there?

So I changed my event procedure to

[Process Date] = Format(Now(), "mm/dd/yyyy")

And this is now putting in the date only, which now shows up on Sunday.

Thank you for the help, there's no telling how long it would have taken me to figure out the cause.

 

[Joe4]

the date is put in mostly with =Now() code after update from another field.

How about just changing it so it uses "=Date()" instead of "=Now()"?
"=Date()" does NOT contain a time component, "=Now()" does.

 

[arcaidius]

That works too. Forgive me, I am a self taught noob.

 

[Joe4]

That works too. Forgive me, I am a self taught noob.

No worries! Just trying to present other ideas that may simplify things.
IMO, if you don't need a time component, then it is best to try to save the data without it, as having the time component can make the fields a bit more cumbersome to work with (as you can see!).