Adding 5 days to date function but exclude weekends

[mulefeathers]

I have a table named "DATA" this table contains thousands of rows. I am writing a query that will allow a user to look at the data that is 5 days out from the request date in the table plus any older data that is know as past due. I can return this information using <DATE() + 5. This works with no problems except Thursday and Friday. We never have orders due on Saturday or Sunday.

Is there a way I can tell this function to avoid these two days and only count "business days"?

[Robeen]

You can use the Weekday() function in your query.

For instance, I just added this field in Query Design View:

Date_Plus_5: IIf(Weekday([StartDate])=1,DateAdd('d',5,[StartDate]),DateAdd('d',7,[StartDate]))

What it does is if the StartDate falls on a Sunday [Weekday([StartDate])=1] - then add 5 days [DateAdd('d',5,[StartDate])] otherwise add 7 days [DateAdd('d',7,[StartDate])].

You'll have to modify this to do what you need.
If you have not used IIF() before, you can have multiple IIF() functions within the IIF() function to handle various criteria.
You can also use the IIF logic in the Critera of your query.

Let us know if you have trouble with it!

All the bset!!