Passing Array into Function from Query

cptnspoon

New member
Local time
Tomorrow, 00:56
Joined
Feb 27, 2007
Messages
3
First up, is it possible to pass a set of values into a function as an array

ie: qryTest has var1 var2 var3 var4

Can I pass it into a function that looks like this:

Code:
Public Function FunctionTest(varArray()) 
       ...etc 
End Function

And just do this in my Query:
Code:
Field1: FunctionTest(varArray([var1], [var2],[var3]))

I know obviously the way I've written it it won't work, but is there some variant of that that will? The reason I'm doing this is I have to pass 50 variables into 1 function (48 monthly comparison variables and 2 identifiers) and Access won't let me do it. What would be another way of doing this that works? I've tried global arrays with no luck (I'm sure I COULD use them but it's a bit sticky) so how can I do this?

Any help is MUCH appreciated!
Thanks
 
Check out "Function Statement" in VBA help. In the arglist you can pass in a 'ParamArray' of variants.
Function declaration looks like...
Code:
Public Function YourFunction(ParamArray items() as Variant)
Call the function using...
Code:
YourFunction("Item1", 2, 3, "Item4", "#1/1/07#")
Determine the length of 'items()' using UBound(items).
 
EDIT: I spoke too soon! Please see below!!!!

Thanks for that lagbolt, works like a charm.

Much appreciated!!:D
 
Last edited:
Oh wait, now that I tried to imlement it I am getting a "The expression you entered is too complex" error in the Query when I try to build the function call (I assume because of the amount of arguments).

Now I'm back to square one, however I have rewritten the function to work (from the VB side) using the ParamArray call so is there anyway to fix this new error?

Thanks in advance
 
Your new problem is tougher to troubleshoot without more info. Try sub queries, but also, passing 50 variables to a function feels wrong. Maybe if you have 50 result fields in a query, open a recordset on that query and pass your parameters to a function one at a time?
Best of luck,
 

Users who are viewing this thread

Back
Top Bottom