dynamic query using parameter function

[ken]

i have a query where i pass via a function a parameter. works fine. for
example query is "select * where x=Function()" This works fine when the
result of Function() is numeric. Function is a vba function which return the
appropriate number. i would like to Function to return In (x,y,z). so instead
of passing a number i want to pass the In statement. how do i do this.
currently i receive a variable mismatch. i think i need to basically have
some type of inline function which converts the In text statement to the
comand?

thks
ken

 

[Dirk Goldgar]

i have a query where i pass via a function a parameter. works fine. for
example query is "select * where x=Function()" This works fine when the
result of Function() is numeric. Function is a vba function which return
the
appropriate number. i would like to Function to return In (x,y,z). so
instead
of passing a number i want to pass the In statement. how do i do this.
currently i receive a variable mismatch. i think i need to basically have
some type of inline function which converts the In text statement to the
comand?

You can't really do that, but you can fake it, if you don't mind that the
query will be much slower. Set up the function so that it returns a String,
which is a comma-separated list of values; e.g., "1,2,3". Then change your
query so that it uses the InStr function to determine whether x is in that
string:


SELECT * FROM MyTable
WHERE InStr("," & Function() & ",", "," & x & ",") > 0

The commas are concatenated so that we end up looking for (e.g.) the string
",1," in the string ",1,2,3,".

As I said, this query will be relatively slow, because the database engine
can't use indexes. However, if you don't have too many records to search
through, that slowness may not be apparent.

 

[Banana]

If this is too slow for you, an alternative is write a function that
will do two thing:

1) Truncate & reload a temporary table with values you want to have in
the "In()"

2) Execute a query that joins the target table with the temporary table.

This will return you the same result as "dynamic IN()".

I'm not sure what you ultimately would do with the query, but if it's
for say, a report, you can have the function open the report with the
updated query as its recordsource.

On other hand, if it's just to append/delete/update a certain set of
records, then that's easy-squeezy. Just execute the query inside the
function, so you only need to call a function in either case.