datediff, count excluding holidays

[Kate]

Hey,
Is it not possible to set up a table in access, say "holidays", with
all the holidays in it, then do a datediff excluding those holidays?

Or, to do it the other way around and just count the holidays, ie, in
the control source on a form, enter something like
=Count(Startdate, Enddate, Holidays)

Do you have to set up a function? If so, how do you do that?

thks for any help

 

[Douglas J. Steele]

Yes, it's certainly doable.

Your holiday table would need to have a row for each holiday, and a field
that identifies the exact date for the holiday, something like:

Holidays
HolidayDate Date/Time (Primary Key)
HolidayName Text

For a given StartDate and EndDate, you can find out how many holidays fall
within the period as:

DCount("*", "Holidays", "HolidayDate Between " & _
Format(StartDate, "\#mm\/dd\/yyyy\#") & _
" And " & Format(EndDate, "\#mm\/dd\/yyyy\#"))

Note that Dates in SQL and functions like DCount must be in mm/dd/yyyy
format (or some unambiguous format: the point is, you can't use dd/mm/yyyy,
regardless of what your Short Date format has been set to through Regional
Settings)

 

[Kate]

Thanks, dcount is a great function!
My next question is, having got that value using "=dcount..." in the
countrol source on the form, how do you make it update the
corresponding table? ie, other fields on my form have a control source
equal to that in the corresponding table, so obviously they update
automatically. I create my reports off the form, so I guess it doesnt
really matter, this is just more for completion than anything else.

 

[Douglas J. Steele]

Calculated fields should never be stored: you can always recalculate them
when needed.

Put the calculation in a query and use the query as the record source for
both the form and the report.

 

[Kate]

So you mean I should calculate my number of holidays in a query and
transfer this directly to the form?

At the moment, I am using the following code in the control source of
the form to count the number of holidays:

DCount("[Holidays]","Holidays","[Holidays] Between [Origination Date]
and [Actual Closure Date]")

(where I have a separate table called Holidays, with the field also
called Holidays)

How would I transfer this to a query? When I just dump the formula in
the field of the query, I get errors...

 

[Douglas J Steele]

What are [Origination Date] and [Actual Closure Date]: fields in the
underlying recordset, or unbound text boxes on the form?

If they're fields in the underlying recordset, try:

DCount("[Holidays]","Holidays","[Holidays] Between " & Format([Origination
Date], "\#mm\/dd\/yyyy\#") & " and " & Format([Actual Closure Date],
"\#mm\/dd\/yyyy\#"))

If they're unbound text boxes on the form, you need to refer to the form

DCount("[Holidays]","Holidays","[Holidays] Between " &
Format(Forms!MyForm![Origination Date], "\#mm\/dd\/yyyy\#") & " and " &
Format(Forms!MyForm![Actual Closure Date], "\#mm\/dd\/yyyy\#"))

 

[Kate]

Terrific! It was an underlying field in the recordset, so your first
option worked.

I used the wizard to create my form from a table, but if I wanted to
show this calculation I've just done in the query on the form, how do I
do that now that the form is already created? ie how do i go back and
say well now this form is dependent on the table and the query?

I tried this in the control source on the form:
=[QIF master Query]![Time to Complete]

but that didnt work. Do I need to add a relationship? Or should I be
able to make the table update itself from the query and therefore I
dont need to change anything on the form?

 

[Douglas J Steele]

So if I understand correctly, you now have a query that that shows the
number(s) you want. Make sure you're using that query as the RecordSource
for your form, then add a new textbox wherever you want the value to show on
the form. Under Properties for that new text box, look at the ControlSource
property, and find the name of the calculated field from your query.

 

[Kate]

The query was not listed as the record source for the form, so i just
made a subform in the form and based that on the query. Thanks for all
you help.