Removing weekend days and holidays from a date range when reporting metrics.
Posted on 2006-06-22
I scanned the database and found some other questions along this line, but could not figure out how to make it work.
I've been asked to report some metrics for the last 'x' business days, so basically I need to figure out a date to use for my report by subtracting ‘x’ from the current date and count back to a beginning date excluding all the weekends and holidays.
On a form (frm_Test) I have . . .
1. . . a text box that holds ‘x.’ (txtNumDays)
2. . . a text box to reflect the date I want to use for reporting: txtRptBeginDate, =DateValue(Now()-[txtNumDays])
3. . . a command button that opens a report and shows the data based on a query that uses the txtRptBeginDate. (>=[Forms]![frm_Test]![txtRptBeginDate])
All works well except I need to not count the weekends and holidays when I go back to the txtRptBeginDate. I can make a calendar table if necessary. Can someone help me with the code to make this work? Thank you!