Not including Weekends and Public Holidays!

[Guest]

I have the follolowing statement but it only excludes Weekends, i would also
like to exclude the federal Holidays..... How do I do that?

CompOnTime:
IIf([tblTaxDoc]![TaxDocStatus]="Complete",(IIf((([tblTaxDoc]![TaxDocName]="W2")
And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd])-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1)<=10)
Or ([tblTaxDoc]![TaxDocName]="1040" And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd]-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1))<=2),"OnTime","OutStd")))

 

[Rick B]

Do a search. "Work Days" or "Exclude Holidays" should find the posts you
want. This is a pretty common topic and has been answered many times.

 

[Graham Mandeno]

Access cannot "know" which days are "federal holidays". Even if it did,
they could change next week at the whim of the government of whichever
country you live in.

You will need to make a table of holidays with the date and (optionally) the
name of the holiday. Then you can check if any given day is a holiday by a
simple lookup to see if the given date is in the table. Additionally, you
can ascertain the number of holidays between two given dates by counting the
number of holiday records between those two dates.

 

[Douglas J. Steele]

As Graham explained, you need to provide that information to Access.

There's one approach outlined in
http://www.mvps.org/access/datetime/date0012.htm at "The Access Web". You
could also check out my September, 2004 and June, 2005 "Access Answers"
columns in Pinnacle Publication's "Smart Access". You can download the
columns (and sample databases) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[James A. Fortune]

I have the follolowing statement but it only excludes Weekends, i would also
like to exclude the federal Holidays..... How do I do that?

CompOnTime:
IIf([tblTaxDoc]![TaxDocStatus]="Complete",(IIf((([tblTaxDoc]![TaxDocName]="W2")
And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd])-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1)<=10)
Or ([tblTaxDoc]![TaxDocName]="1040" And
DateDiff("d",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd]-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],7)-DateDiff("ww",[tblBorrower]![RqstDateRcvd],[tblTaxDoc]![TxDocDateRcvd],1))<=2),"OnTime","OutStd")))

The following thread shows where I'm headed on this issue:

http://groups.google.com/group/comp...163f0/86afb53893e1a4c7?hl=en#86afb53893e1a4c7

My holiday functions were explained in this thread:

http://groups.google.com/group/comp...2a1a72d1daa/2b195d1eaf40ec7f#2b195d1eaf40ec7f

James A. Fortune
(e-mail address removed)