Networkdays function in Access

[Joey]

I use the Networkdays function in Excel to determine the
number of work days between 2 dates while excluding
holidays. I would like to do this in an Access query
instead. The DateDiff function serves all my purposes
except for the exclusion of holidays. Is there a way to
get around this or a different function to use?

Thanks.

 

[Michel Walsh]

Hi,


Sure, take a look at http://www.mvps.org/access/datetime/date0012.htm



Hoping it may help,
Vanderghast, Access MVP

 

[Joey]

Thanks for the suggestion. I actually found my way to
that page yesterday, and found the necessary code,
however, I'm having trouble linking to it to a field in a
query/form/report (anything at this point!)

Any suggestions?

 

[Michel Walsh]

Hi,


In a query, as computed expression like:

NumberOfWorkingDays: dhCountWorkdaysA( StartingDateField, EndingDateField,
....optional_constant_list_of_holidays... )


like:

NumberOfWorkingDays: dhCountWorkdaysA( Start, Ending, #01-01-2004#,
#01-01-2005#)


as example



Hoping it may help,
Vanderghast, Access MVP

 

[Joey]

Ok, let me first say that I REALLY appreciate your help
with this. I tried typing in the expression you gave me,
and I'm getting an error when I run the query saying that
I am using an undefined expression. Is this something
specific to my version of Access, or am I just way off?
Do I maybe not have a full install?

-----Original Message-----
Hi,


In a query, as computed expression like:

NumberOfWorkingDays: dhCountWorkdaysA(

StartingDateField, EndingDateField,

 

[Michel Walsh]

Hi,


You must use real field name instead of Start and Ending.


You must be sure dhCountWorkdaysA is in a standard module (not behind a
form, not behind a class).


If you still have an error, "who", "what expression" is reported
"undefined" ?



Vanderghast, Access MVP

 

[Ellen]

Hi,

I was delighted to see this thread, as I want to use the
same function and am also encountering an error.

I get the following error:

"Data type mismatch in criteria expression."

The function, with my info in it, looks like this:

CalcDaysToProc: dhCountWorkdaysA([dtmDateReceived],
[dtmDateProcessed],Array
(#1/1/2004#,#5/31/2004#,#7/4/2004#,#9/6/2004#,#11/25/2004#
,#11/26/2004#,#12/25/2004#))

[dtmDateReceived] and [dtmDateProcessed] are formatted as
Short Dates.

Can you please help me identify my error?

Thank you very much!

Ellen

 

[Michel Walsh]

Hi,



Can you try, in the debug-immediate window:


? dhCountWorkdaysA(#1-1-2004#, Now,
Array(#1/1/2004#,#5/31/2004#,#7/4/2004#,#9/6/2004#,#11/25/2004#,#11/26/2004#
,#12/25/2004#))



If that works, the problem is probably with one of the first two arguments.
Try then


dhCountWorkdaysA( CDate(dtmDateReceived), CDate(dtmDateProcessed), Array(
....) )



Hoping it may help,
Vanderghast, Access MVP

Hi,

I was delighted to see this thread, as I want to use the
same function and am also encountering an error.

I get the following error:

"Data type mismatch in criteria expression."

The function, with my info in it, looks like this:

CalcDaysToProc: dhCountWorkdaysA([dtmDateReceived],
[dtmDateProcessed],Array
(#1/1/2004#,#5/31/2004#,#7/4/2004#,#9/6/2004#,#11/25/2004#
,#11/26/2004#,#12/25/2004#))

[dtmDateReceived] and [dtmDateProcessed] are formatted as
Short Dates.

Can you please help me identify my error?

Thank you very much!

Ellen

-----Original Message-----
Hi,


You must use real field name instead of Start and Ending.


You must be sure dhCountWorkdaysA is in a standard module (not behind a
form, not behind a class).


If you still have an error, "who", "what expression" is reported
"undefined" ?



Vanderghast, Access MVP




.