Networkdays in Access

  • Thread starter Thread starter Ellen
  • Start date Start date
E

Ellen

Hi,

I was delighted to see this thread, as I want to use the
same function. I added to the thread in its original
location, but then worried that it was so old that I
might not get a response. So I copied it here. Hope
that's OK.

I got the code from the site indicated, but am also
encountering an error, although a different one.

I copied the entire set of code from the Web site to
Module One.

I entered the function below in a column of a query based
on the table containing the Start and End dates.

I get the following error:

"Data type mismatch in criteria expression."

The function, with my info in it, looks like this (copied
straight from the query field):

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
Date/Time 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




.
Click to expand...
..
 
Ellen,

The problem comes from trying to use an Array function in a 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


Ellen said:
Hi,

I was delighted to see this thread, as I want to use the
same function. I added to the thread in its original
location, but then worried that it was so old that I
might not get a response. So I copied it here. Hope
that's OK.

I got the code from the site indicated, but am also
encountering an error, although a different one.

I copied the entire set of code from the Web site to
Module One.

I entered the function below in a column of a query based
on the table containing the Start and End dates.

I get the following error:

"Data type mismatch in criteria expression."

The function, with my info in it, looks like this (copied
straight from the query field):

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
Date/Time 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




.
Click to expand...
.
Click to expand...
 
Cheryl,

That worked! Thank you!

Ellen Burd
-----Original Message-----
Ellen,

The problem comes from trying to use an Array function in a 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


Hi,

I was delighted to see this thread, as I want to use the
same function. I added to the thread in its original
location, but then worried that it was so old that I
might not get a response. So I copied it here. Hope
that's OK.

I got the code from the site indicated, but am also
encountering an error, although a different one.

I copied the entire set of code from the Web site to
Module One.

I entered the function below in a column of a query based
on the table containing the Start and End dates.

I get the following error:

"Data type mismatch in criteria expression."

The function, with my info in it, looks like this (copied
straight from the query field):

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
Date/Time 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"
Click to expand...
is
reported
"undefined" ?



Vanderghast, Access MVP

Ok, let me first say that I REALLY appreciate your help
with this. I tried typing in the expression you
Click to expand...
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,
....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



message
Thanks for the suggestion. I actually found my
Click to expand...
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?

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


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



Hoping it may help,
Vanderghast, Access MVP


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
Click to expand...
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.


.



.



.
Click to expand...
.
Click to expand...


.
Click to expand...
 
Back
Top