Date()-3 **but** exclude weekends

[kpatrickwv]

Hey, all. I'm looking for a little bit of help. I need to run a query that will look for all data from before three days prior to today. All data gets an automatic 72 hour lag, but only business days count to this.

For instance, running Date()-3 on Tuesday would not exclude data from Friday or Thursday: but I need it to do so.

I tried following and tweaking the instructions here:
thread: t=193459 (can't post links yet)

But was unable to make that work properly.

My thought process was to use the Criteria field to restrict the days something like: Date()-3 if and only if date() is thurs, fri. Date()-5 if and only if date() is wed, tues. Date()-5 if and only if date() is tues. Date ()-6 if and only if date() is mon.

Thoughts on making this work?

 

[plog]

You could cram this all into 1 expression in a query, but I advise against it. You should create a VBA function that returns the date you want. Here's some psuedo code to get you started:

get_RunDate() As Date
    ' returns date of 3 business days ago, from today 


int_DaysToSubtract = 0
    ' will hold days to subtract to arrive at 3 business days ago

 
Switch(Weekday(Date())
    ' gets number of today's day and adjusts int_DaysToSubtract accordingly

    2: int_DaysToSubtract = -6
    3: int_DaysToSubtract = -5
    etc.

end switch

get_RunDate = DateAdd()
    ' use function to subtract days from today to return 3 business days ago date

End Function

Then you use that function in your query criteria:

>=get_RunDate


Notice the 2 functiosn I used WeekDay (http://www.techonthenet.com/access/functions/date/weekday.php) and DateAdd(http://www.techonthenet.com/access/functions/date/dateadd.php). Check out those links for help with them.