Trying to automatically report on last WORK day, NOT Yesterday

[Graham Mandeno]

You could use the Choose function to decide how many days to subtract:
Date()-Choose(WeekDay(Date()), 2, 3, 1, 1, 1, 1, 1)

In other words, subtract two days if today is Sunday, three for Monday,
otherwise one.

 

[rgrantz]

I found some really good code out there for calculating workdays between 2
dates, etc., but couldn't see from the code how to set criteria of a query,
or an unbound control, to use the last WORKDAY (Monday's date if today's
Tuesday, but Friday's date if today is Monday).

I feel like I got close using the Weekday(Date,vbMonday) function, but when
I started using If's and things to check for the number returned being 6 or
7, and then doing all these DatePart and things to add onto the results, I
thought maybe I should see if someone knows a much cleaner way of doing it
without 14 Select Cases and 10 DatePart add-ons and such.

So basically, I just want to replace Date()-1 with
(Something-that-means-last-workday). Workdays here, by the way, are Monday
through Friday.

Thanks for any help, you've all been great