DATE PARAMETERS (Access 2000)

Topic

New Reply

1. I have a report that pulls data from a query that has a date field with this criteria:
Between [Enter Start Date] And [Enter End Date].
Is it possible to display on the report the dates that are entered in the Parameter Boxes that appear as criteria?

2. I have a table with 3 fields: PeriodID (long integer, auto number), PayStart (date field), and PayEnd (date field). The PayStart field has dates that correspond with the 1st and 15th of each month. The PayEnd field has dates that correspond with the 16th and whatever day is the last day of a particular month (e.g. 28th, 30th or 31st).
Can Access; give me the number of days between two dates (excluding Saturdays and Sundays) inclusive of of each date?
I have seen the NETWORKDAYS function, but it only works in EXCEL.
Does Access have a function that performs the same operation?

Reply | Quote

Replies

(1) Yes, you can refer to the parameters in expressions. For instance, create a text box on the report with ControlSource
=[Enter Start Date]
and another with ControlSource
={Enter End Date]
or one text box with ControlSource
=”Dates between ” & [Enter Start Date] & ” and ” & [Enter End Date]
If this doesn’t work, open the query in design view, select Query/Parameters…, and enter the parameters just as you typed them in the criteria line, and set the data type to Date/Time.

(2) Access doesn’t have a built-in function for this. See post 159335 for a simple function that counts the number of days excluding weekend days. It doesn’t take holidays into account. If you need that too, see the links in post 159312 in the same thread.

Reply | Quote

Hans,
Thanks for the information, but I must confess that I have no idea where this function should go in my report’s VBA module and what modifications must be made for it to work properly.
Could you pleae give me some pointers?

Reply | Quote

Access has a great interface with your keyboard: If you accidentally hit the F1 key, a help system appears, full of useful information. It’ll even give you samples. You don’t even need to be logged onto the net to use that feature . Try it, that’s how I learned the basics of this product.

Reply | Quote

Thank you Hans and Claude for the information. It works great! That F1 key is pretty cool!

Reply | Quote