Last Business Invoice Date

[67159]

I have a query that runs a customer's two accounts invoices. I need to set up the invoice date to automatically pull data only from the last business date. The Date()-1 in the Invoice Date criteria cell works fine except for Monday's. Then it pull the Sunday data which there is none. Any ideas? I don't want to manually have to type in the last business date and I really don't want to run the query on Saturday morning if you know what I mean. Here's a cut away view of my query. Thank you and have a great rest of the day.

[June7]

So if Monday then -3, else -1.

Use Weekday() function and an IIf() expression.

[67159]

Wow! I just learned to stay online and connected.

I first ran the query and retrieved the data to make sure I had hits. Then I wrote it as IIF("()vbMonday",-3,-1), but returned no data. What did I do wrong?

[June7]

IIf(WeekDay([fieldname])=2, -3, -1)

[67159]

Thanks for the layout, June7. I tried it verbatim and a few other ways, but nothing returned the data. It's got to be a field format issue. And I have no control of that. So I copied the query and set the Invoice Date criteria as Date()-1 and named it Monday Override Report. It's cumbersome, but it works. Thank you for the effort. Have a great rest of the day.

[June7]

Sorry, suppose need to use Date() instead of fieldname:

Date() - IIf(WeekDay(Date())=2, 3, 1)