Networkdays Function in Access?

[Lauren]

Hi All -

Networkdays works great in Excel, and Access help leads me
to believe that it is available in Access, but I can't
seem to make it work.

Is the Networkdays function available in Access? If not,
any suggestions for how I can return the difference
between two dates, but ignoring weekends and holidays?

Thanks tons!! Lauren

 

[Cheryl Fischer]

I don't believe that you can use the Excel function directly; however, check
out the code at The Access Web:

http://www.mvps.org/access/datetime/date0006.htm

And, there is a SkipHolidays function here:

http://www.mvps.org/access/datetime/date0012.htm

 

[Guest]

Cheryl

I am trying to use the dhCountWorkdaysA function. How do I tell it to use a table for the array

thx

 

[Cheryl Fischer]

There have been a number of solutions and variations posted in the past year
or so for this function. You can review about 3 pages of them by doing a
search at http://groups.google.com using dhCountWorkdaysA as the
criterion for searching.

Here is a variation by Doug Steele: http://tinyurl.com/3ds6s that may help
you.

 

[Guest]

Awesome! I did not know to go there. thx!

 

[Guest]

Sorry, but can you tell me why I get a Data Type Mismatch in criteria expression error with the following SQL

SELECT tblSCSAR.BeginVerification, tblSCSAR.QASCMVerified, dhCountWorkdaysA([BeginVerification],[QASCMVerified], Array (#3/5/2004#, #3/19/2004#)) AS Duratio
FROM tblSCSA
WHERE (((tblSCSAR.BeginVerification)>=DateSerial(Year(Date()),Month(Date())-1,1) And (tblSCSAR.BeginVerification)<DateSerial(Year(Date()),Month(Date()),1)) AND ((tblSCSAR.QASCMVerified) Is Not Null));

 

[Cheryl Fischer]

The problem comes from trying to use an Array function in the query. Here
is a link to a work-around posted by MVP John Viescas:

http://tinyurl.com/328a7

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Sorry, but can you tell me why I get a Data Type Mismatch in criteria

expression error with the following SQL?

SELECT tblSCSAR.BeginVerification, tblSCSAR.QASCMVerified,

dhCountWorkdaysA([BeginVerification],[QASCMVerified], Array (#3/5/2004#,
#3/19/2004#)) AS Duration

FROM tblSCSAR
WHERE

(((tblSCSAR.BeginVerification)>=DateSerial(Year(Date()),Month(Date())-1,1)
And (tblSCSAR.BeginVerification)<DateSerial(Year(Date()),Month(Date()),1))
AND ((tblSCSAR.QASCMVerified) Is Not Null));