Q: I need a query to return only a list of business days. For example, if the date of a work order is on a Saturday, then I need the system to treat it as if it's on the following business day (Monday).


A: Use the Weekday formula to determine the day of week. 1=Sunday, 2=Monday, etc. 7=Sunday. Then, just create a simple calculated query field to add 2 to your date if the day is Saturday, or 1 if it's Sunday.

Here's a calculated query field formula, for example, that will take a date MyDate and give you the next business day NewDate:

NewDate: IIf(Weekday([MyDate])=1,[MyDate]+1,IIf(Weekday([MyDate])=7,[MyDate]+2,[MyDate]))

Now just place that into your query, and you should have a list of valid "business day" dates for your work orders.

Subscribe to Access: Next Business Day
Get notifications when this page is updated