VBA Function that outputs a criterion for a query

[roger]

What’s the difference in these two situations?

First I have a VBA Function that outputs a string I use as a criterion in a
query:
(to simplify

Let’s say the output of funcCrit() is “ABCâ€
Then I have a query with a criterion of :
Like “*â€& funcCrit() & “*â€
That works and finds all the records with 12ABC34 and the like.

But then I wanted a more complex query.
So wrote a different func.
This one outputs a more complex string that INCLUDES the operands and the
quotes:

Let’s say the output of funcComplexCrit() is: Like “*ABC*4*â€
Now I have a query with a criterion of just:
funcComplexCrit()

But that doesn’t work.

I know my function outputs a valid criterion.
If I goto the immediate window and type:
?funcComplexCrit()
I get:
Like “*ABC*4*†(including all the quotes in the right places)
and if I copy and paste that into the query instead of the function call, it
works.
But the function call itself doesn’t.

What have I done wrong?
I tried declaring funcComplexCrit to be a string:
Function funcComplexCrit() as string. (that is “declaring†isn’t it?)
but that didn’t help.
Maybe it should be something else, a variant? An object?

Aha tia

 

[Marshall Barton]

What’s the difference in these two situations?

First I have a VBA Function that outputs a string I use as a criterion in a
query:
(to simplify

Let’s say the output of funcCrit() is “ABC”
Then I have a query with a criterion of :
Like “*”& funcCrit() & “*”
That works and finds all the records with 12ABC34 and the like.

But then I wanted a more complex query.
So wrote a different func.
This one outputs a more complex string that INCLUDES the operands and the
quotes:

Let’s say the output of funcComplexCrit() is: Like “*ABC*4*”
Now I have a query with a criterion of just:
funcComplexCrit()

But that doesn’t work.

I know my function outputs a valid criterion.
If I goto the immediate window and type:
?funcComplexCrit()
I get:
Like “*ABC*4*” (including all the quotes in the right places)
and if I copy and paste that into the query instead of the function call, it
works.
But the function call itself doesn’t.

What have I done wrong?
I tried declaring funcComplexCrit to be a string:
Function funcComplexCrit() as string. (that is “declaring” isn’t it?)
but that didn’t help.
Maybe it should be something else, a variant? An object?

A function returns a **value**, not a part of an expression.
The query is trying to use the function's result as a value
and it is not going to try to compile smoe concatenation
expression into a criteria expression. You need to do
something to evaluate the resulting criteria expression.

The most obvious approach is to modify the function to do
it. E.g.

Public Function funcCrit(fieldvalue As Variant) As Boolean
funcCrit = (fieldvalue Like “*ABC*4*”)
End Function

Then the query would use:
WHERE funcCrit(thefield)

Another approach is to stick with your funcComplexCrit
function and use the Eval function in the query:
WHERE Eval(thefield & funcComplexCrit() )