Using Function w/Array in Query

  • Thread starter Thread starter Tony
  • Start date Start date
T

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
 
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
 
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
 
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
 
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
 
Back
Top