Using Function w/Array in Query

[Tony]

Hi All,

I have a table that has a column I'd like to populate based on the result of
running a function through an update query. Don't know if this can be done,
but here goes...

The column (Success) has three possible values, Yes, No, and NA. I envision
having three similar queries that add one of these values based on the
following:

1) If a date (StartDate) + 5 working days is greater than a second date
(CompDate), then Success = Yes
2) If StartDate + 5 is less than CompDate, then Success = No
3) If there is no CompDate, then Success = NA

I have a function that works when used in a form, to determine which days
are working days, including days we define as holidays. It takes as input:

working days (Long) 'in this case, working days = 5
start date (Date) 'StartDate
holidays (Variant) 'the dates are stored in an array,
holidays=Array(#1/1/2004#, #7/4/2004#, #12/25/2004#)

The problem I'm running into is that I don't know how (or if) I can get this
array of holidays into the function when it's used in the query. No matter
how I try to enter the dates, I get a Data Type Mismatch error.

If anyone can point me in the correct direction, I'd much appreciate it.
It'll save me a lot of time.

Thanks in advance & Ciao,

Tony

 

[Michel Walsh]

Hi,


You may try to send the data in a run time made IN list, or, preferably, in
a table.


Hoping it may help,
Vanderghast, Access MVP

 

[Tony]

Hi Michel,

Thanks for the response. I've found a way around needing to do this: just
got the user to limit the data set that needed to be updated to the previous
month instead of the previous 5 years. However, I don't understand what you
mean by sending the data in run time made IN list or table. Although I
won't need it, I'd still like to understand it. If you can give me some
details, I'd appreciate it. It may be useful in the future.

Thanks,

Tony

 

[Michel Walsh]

Hi,

At run time, you build a string that represents the SQL statement, so,
you just append one after the other, the values, as part of an IN list, to
get something like:

"... WHERE field IN( #1/1/2004#, #7/4/2004#, #12/25/2004#) "



Hoping it may help,
Vanderghast, Access MVP

 

[Tony]

Hi Michel,

I think I understand now. Seems like this would work for what I was trying
to do and more flexible that the way I was trying to do it.

Thanks for the tip & the help. I appreciate it.

Ciao,

Tony