Easy one... Query that specifies doing one thing on Monday and another the rest of the days.

[Bear505]

I want to run an update query to update yesterdays data. so in the Criteria field I input Date()-1. Except on Monday, it needs to say Date()-3.



I've tried something like this, but I'm not sure if I'm getting the right return.

IIf(Weekday(1),Date()-3,Date()-1)

I'm trying to say If, its Monday, use Date()-3, if it's Tuesday - Friday, use Date()-1

I'm not a VBA writer, so if you could specify what to put in the Query, Criteria field, I'd really appreciate it.

Thanks in advance.

[pbaldy]

More like

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

[June7]

Not using Weekday function correctly.

The default first day of week is Sunday, so Monday would be day 2.

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

Absolutely certain will never run on Sat or Sun?

EDIT: Okay, I DID NOT copy/paste Paul's expression - great minds ...

[Bear505]

Ok, just so I understand... (for next time) I'm telling this that If today is Monday (Day #2) then I want to update from Friday's Data. If its Tuesday through Friday, then I want to update from yesterday's Data.

Bear with me here..

so if it's the Weekday's Date=2 (or Monday), subtract 3 days from today (or Friday), else just subtract 1

Learn Something new everyday...

Thanks guys.. Just made my Monday's a lot easier.. LOL

[pbaldy]

Happy to help!