Not able to insert user defined function in Query Criteria

[Nistin27]

Hi,

I have following Combo lists in my form
Priority: G1, G2, G3
Color: R, G, Y
I have written two functions to get the criteria when a user selects more than one entry at a time.
Like if user selected, Priority as G1, or G2 and Color as R.
then the table should filter out the records as per above criteria.

But when I insert this function in Criteria row, and I run the query it doesn't any records.
To verify the function, I have opened a new form and displaying the returned value in a text and it's works properly but as soon as I insert the function nothing is returned. While If I copy the same criteria returned in text filed in criteria field in Query, it works fine as expected and returns all the values.

Please help me

[NoellaG]

Hi,

try to use the eval function
Eval(MyUserFunction())

greetings
NG

[Nistin27]

No NG, it's not working. I still don't see any results

[DaleM]

I am also having the same problem. I would like to use the result of a user-defined function as the criteria for my query.

The UDF (string type) is designed to take user inputs (check boxes) on a form to create the string, which is a series of market segment abbreviations connected by OR operators (for example if the user selects the Consumer and Food Service checkboxes, the string will evaluate to: 'C' OR 'FS'.

If I evaluate the function and paste the results into the criteria line of the query, it works perfectly. When I input the UDF as the criteria, it doesn't work. Any ideas would be greatly appreciated.

[pbaldy]

This is less an answer than an attempted explanation, using my likely flawed understanding of what's going on under the hood. If you type 'C' OR 'FS' into the criteria grid in design view, tab out of that field and switch to SQL view, you'll see that the design grid created the "proper" SQL of

WHERE FieldName = 'C' OR FieldName = 'FS'

In other words, the design grid takes your input and converts it into proper SQL for you. When you use the function, design view isn't doing the work for you anymore, so the SQL JET tries to use is

WHERE FieldName= 'C' OR 'FS'

which won't return the desired result. I'd probably build SQL in code, but this is a workaround I've seen used (if I've interpreted it correctly). Modify your function to return ";C;FS;" and try this in the query:

WHERE InStr(FunctionName(), ";" & FieldName & ";") > 0

[DaleM]

Pbaldy,

Thanks! The work around worked great.

I didn't try writing in SQL, but it looks like I won't need to.

Thanks for your help.

[pbaldy]

Happy to help! Glad it worked for you.