Sending multiple criteria to a query via a function (1 Viewer)

[bretto]

Hi,

I was looking for an answer to the following problem and stumbled here so I thought I might as well dive right in and ask!!!

One of the reports in my application is based on a query that gets it's criteria from a vba function, not a problem until I wanted to use the Or operator in the aforementioned criteria.

i.e. the function previously would only return a single value 4421 but now I want the function to return 4421 or 5584 or 1274.

The function returns a string so I thought I could just build my string with the Or operator included e.g. "4421 or 5584 or 1274" but you get a criteria mismatch.

Any help would be much appreciated

bretto

 

[WayneRyan]

bretto,

A function can return a variant, which could be an array,
but I think that you will still encounter the "type mismatch"
message if you use it.

A work-around might be to put the variant's contents into
some unbound controls on your form. Then you can reference
them in your query.

Maybe the best option would be to dynamically build your
SQL string for the query using the following:

Select SomeFields
From YourTable
Where YourKey In (1111, 2222, 3333);

The hard part would be traversing your variant and
building the string (1111, 2222, 3333).

Another idea might be to have your function populate
a table and use:

Select SomeFields
From YourTable
Where YourKey In (Select TheKey From TempTable);

Just some thoughts.
Wayne

 

[bretto]

Wayne,

Thanks for the reply. I was hoping there was a nice easy way to do this but I don’t think there is.

I think I’ll have to have to use a temporary table to store the criteria as much as it annoys me to do so!! Preferably I’d build a SQL string for this type of problem but the query in question is very large and complicated therefore making future maintenance a problem, also the criteria is not used in the final query but in a feeder query a couple of ‘layers’ down.

Bretto