CODEWITHSUNDEEP
vbams-access
Luke101
Luke101

Reputation: 65238

Get yesterdays date and if Monday get weekend range

Is there a way to get yesterdays date. If the current date is monday I need three dates returned - Sunday, Saturday and Friday. Is there any way possible to accomplish this in a single query. I don't know VBA that well but if that is the only way to solve I am willing to get my hands dirty.

Select * from [Purchase Order] where MyDate = 'Yesterdays date(s)'

Upvotes: 2

Views: 2241

Answers (1)

HansUp
HansUp

Reputation: 97101

The WeekDay() function will tell you whether today's date, as returned by the Date() function, is Monday. Use that in an IIf() expression so that MyDate matches yesterday's date when today is not Monday, or the previous 3 dates when MyDate is Monday.

SELECT *
FROM [Purchase Order] AS p
WHERE
    IIf(Weekday(Date()) = 2,
        p.MyDate BETWEEN DateAdd('d',-3,Date())
            AND DateAdd('d',-1,Date()),
        p.MyDate=DateAdd('d',-1,Date())
        );

Upvotes: 4

Related Questions