RLN said:
I am modifying a query where I need to provide the user with a "From" and a
"To" date input boxes, then run the query based on those two dates as filter
criteria.
The user also wants to return the total number of calendar days *and* total
number of business days between those two dates the user chose.
Does anyone have some VBA examples of how to do this?
Can this date range also be used on a report object or can this only be done
on queries?
Since a report is normally based on a query, filtering the
report's query is the normal way to apply a date range (or
any other kind of) criteria. A quick and dirty way to do
what you asked is to use parameter prompts in the query by
setting the date field's criteria to something like:
Between [Start Date] And [End Date]
A report header text box can then display the calendar days
can by using an expression like:
=DateDiff("d", [End Date], [Start Date])
"Business days" is not a well defined term and can be very
complex and highly dependent on your business's definition
of them. If you can totally ignore holidays, then you can
use an text box expression like:
=5 * [txtCalendarDays] / 7
but, if that's not sufficent, you will have to maintain a
table of your business's holidays over a significant number
of years. You can then create a function that uses a
recordset to calculate the number of non-weekend holidays
within a date range. This is definitely not a simple
problem.
Before long, you will find that parameter prompt queries are
not the panacea thay may at first appear to be. The full
approach is to create a form with text boxes for users to
enter the parameter values along with a button to p[en the
report. With this technique, the query criteria would look
like:
Between Forms!theform.txtStart And Forms!theform.txtEnd
