Using the ARRAY function in a query expression

[Carl Imthurn]

I am using some code found on www.mvps.org that calculates the number of
workdays (Monday-Friday) between two dates and (optionally) allows for
and excludes holidays by using an array of holiday dates.

According to the comments in the code, you can type this into the
immediate window:

? dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, Array(#1/1/2000#,
#7/4/2000#))

and it will return 2, because 7/2/2000 is a Sunday, 7/4/2000 is a
holiday, leaving 7/3 and 7/5 as workdays.

It works perfectly.

However, when I attempt to use this in a query expression as follows:

NumberOfWorkdays:dhCountWorkdaysA([FromDate],[ToDate],Array(#7/4/2002#,
#9/2/2002#, #11/28/2002#))

I get the error message

Data type mismatch in criteria expression.

However, if I remove the Array of holiday dates as follows:

NumberOfWorkdays:dhCountWorkdaysA([FromDate],[ToDate])

It works great - but you don't get the holidays calculated.

What am I missing? Can you not use the Array function in a query
expression? The code itself seems to be working great - what the query
doesn't like is the Array function.

Any help will be appreciated. The actual code can be found at
http://www.mvps.org/access/datetime/date0012.htm - I wasn't sure about
the legality of re-posting it here.

Thanks in advance,

Carl

[John Viescas]

The problem is the Array function isn't supported by the query expression
service. However, you can build a custom function as a wrapper:

Public Function MyCountWorkDays(FromDate As Date, ToDate As Date) As Integer
MyCountWorkDays = dhCountWorkDays(FromDate, ToDate, Array(#1/1/2000#,
#7/4/2000#))
End Function

If you need to dynamically generate the array from the query expression
call, you could code your "wrapper" with a series of Optional parameters
that you then assemble into the Array call. If the only variable is the
year, you could pass that as a simple parameter from the query and then use
that to generate a "standard" list of holidays for the specified year.

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Carl Imthurn" <cImt...@sd.llu.edu> wrote in message
news:3DDBD5CB...@sd.llu.edu...

[Carl Imthurn]

Thanks for your time and your solution, John - I appreciate it.

Carl