Query criteria as function

[Sandro997]

I'm just trying to work out why you can't reproduce my problem. I'm thinking it's a bug in version 2003. Try following these steps.

1. Create query: save as qryTheTest
2. In SQL view of this query type: SELECT * FROM [some non-empty table you have] WHERE SetUserGroupCri teria()
3. Create a Function in one of your modules called SetUserGroupCri teria()
4. Populate that function with:

Public Function SetUserGroupCriteria() As String[INDENT]SetUserGroupLikeCriteria = "[some valid string field in your table] Like ""NONEXISTENTSTRING"""[/INDENT]
End function

5. Place a breakpoint in that function to confirm it is being called, and is returning: "[Field] Like "NONEXISTENTSTR ING""
6. In qryTheTest activate datasheet view
7. I predict you will get a list of every record in the table you specified, when you should have received an empty set

[NeoPa]

This is a generic problem anyway.
Now I understand what you're trying to do I know where it's at.
When you say 'WHERE Function()' in a query, it does not, as you expect, resolve the function into a part of the SQL.
Instead, it gets the return of the function and tries to resolve that to a boolean (TRUE or FALSE).

That's why all the questions earlier about the code - this concept can work in code ONLY.

Within a query, as you have it, your only option, as far as I see it, is to write a function that returns TRUE or FALSE for every record (as the query is called per record line of the dataset (In VBA you can call the function once and add the result into the string that LATER is executed as SQL instructions).

[Sandro997]

Ahh ya, that's what I thought. I was just really hoping that there was some workaround. Thanks for your effort though.

[NeoPa]

No problem Sandro.
It's nice dealing with someone who's able to understand what you're trying to say ;).

[Sandro997]

Eh? Are you saying you could understand me or vice versa?...or both?

[NeoPa]

Eh? Are you saying you could understand me or vice versa?...or both?

You seemed able to make intelligent replies to my posts - I'd gathered that you understood most of what I was saying.
I think I understood most of what you were saying so that's a 'Both'.
Everyone should be happy :)