Query criteria as function

[Sandro997]

SELECT * FROM tblLabor WHERE tblLabor.UserGroup=SetUserGroupCriteria()

I have something similar to this in a query that I'm using. I want SetUserGroupCriteria() to return something along the lines of
[Like "CA*" and Not like "RN*"] Unfortunately SetUserGroupCriteria() returns a string that looks like ["Like "CA*" and Not like "RN*""], and the keyword Like gets thrown in front of this whole mess automatically, thus making it say:
Like "Like "CA*" and Not like "RN*"". Needless to say, this is not desirable. Is there anything I can do to correct his?

[VALIS]

SELECT * FROM tblLabor WHERE tblLabor.UserGroup=SetUserGroupCriteria()

I have something similar to this in a query that I'm using. I want SetUserGroupCriteria() to return something along the lines of
[Like "CA*" and Not like "RN*"] Unfortunately SetUserGroupCriteria() returns a string that looks like ["Like "CA*" and Not like "RN*""], and the keyword Like gets thrown in front of this whole mess automatically, thus making it say:
Like "Like "CA*" and Not like "RN*"". Needless to say, this is not desirable. Is there anything I can do to correct his?

Hi Sandro997,
Is SetUserGroupCriteria used anywhere else?
If it isn't it might easier to refine this than mess around re-formatting the text string. Could you paste the code for the function SetUserGroupCriteria()?

Thx

[NeoPa]

If you post the code for SetUserGroupCriteria()
then we can tell you.
It will propably be reasonably straightforward.

[Sandro997]

Hi Sandro997,
Is SetUserGroupCriteria used anywhere else?
If it isn't it might easier to refine this than mess around re-formatting the text string. Could you paste the code for the function SetUserGroupCriteria()?

Thx

Ya, this function is used all over the place.

Public Function SetUserGroupCriteria() As String
Select Case GetUserGroupByUserID(fGetUserID())
Case "UserType1" SetUserGroupLikeCriteria = "Not like ""CA*"" and Not like ""RN*"""
Case "UserType2" SetUserGroupLikeCriteria = "Not like ""LS*"" and Not like ""FM*"""
Case "UserType3" SetUserGroupLikeCriteria = "Not like ""FM*"""
Case "UserType4" SetUserGroupLikeCriteria = "Like ""*"""
Case "UserType5" SetUserGroupLikeCriteria = "Like ""CA*"" and Like ""FM*"""
End Select
End function

There is a fair bit of logic in there
Hope this helps

[NeoPa]

The format you are looking for will only work from within design view of a query.
In SQL you must use something like :-

WHERE [Field] Not Like 'CA*' And [Field] Not Like 'RN*'

Here is your function recoded, but you will need to adjust the calling code with this in mind.

Public Function SetUserGroupCriteria(strField as String) As String
    Select Case GetUserGroupByUserID(fGetUserID())
    Case "UserType1"
        SetUserGroupLikeCriteria = "([" & strField & "] Not Like 'CA*' And [" & strField & "] Not like 'RN*')"
    Case "UserType2"
        SetUserGroupLikeCriteria = "([" & strField & "] Not Like 'LS*' And [" & strField & "] Not like 'FM*')"
    Case "UserType3"
        SetUserGroupLikeCriteria = "([" & strField & "] Not Like 'FM*')"
    Case "UserType4"
        SetUserGroupLikeCriteria = "([" & strField & "] Like '*'"
    Case "UserType5"
        SetUserGroupLikeCriteria = "([" & strField & "] Like 'CA*' And [" & strField & "] like 'FM*')"
    End Select
End function

[Sandro997]

It is the case that the query is in design view. I simply represented the SQL equivalent, since text is easier to display.

[Sandro997]

I've modified the SQL to something akin to:
SELECT * FROM tblLabor WHERE SetUserGroupCriteria()
and SetUserGroupCriteria() returns "([UserGroup] Like 'skizzle*')" This is for testing purposes, there is no row in the db where this should match. Unfortunately, this returns every record in the table. If I replace it with
SELECT * FROM tblLabor WHERE ([UserGroup] Like 'skizzle*'), then it is fine(ie. returns empty set). This kinda baffles me. I think it is relevant to note that this is an access query, as in it is saved as qryGetLaborStuff, and I am working the SQL through this medium, if I convert to design mode this turns into
Field: SetUserGroupCriteria()
Criteria: <> False

[NeoPa]

Very strange!
Could you post the code that builds up the SQL string.
Also, can you post the actual SQL string that you attempt to execute.
(Don't forget to TAG around the code - It's easier to work with if not 'doctored' by the web page parser.)

[Sandro997]

Hmm, could you clarify your request? The code that builds the SQL string...my SQL string exists in an access query. I'm not certain if there is a misunderstanding here, but an access query does not consist of a coding language. It is a GUI for creating queries. The actual string is static, not dynamic, within this query 'file'. The string I am literally using is

SELECT * FROM tblLabor WHERE SetUserGroupCriteria()

Unfortunately this query never exists in code, such as the VBA language...

[Sandro997]

Sorry, repost, edit is disabled.

Hmm, could you clarify your request? The code that builds the SQL string...my SQL string exists in an access query. I'm not certain if there is a misunderstanding here, but an access query does not consist of a coding language. It is a GUI for creating queries. The actual string is static, not dynamic, within this query 'file'. The string I am literally using is

SELECT * FROM tblLabor WHERE SetUserGroupCriteria()
it should be equivalent to
SELECT * FROM tblLabor WHERE ([UserGroup] Like 'skizzle*')
What it is equivalent to is
SELECT * FROM tblLabor WHERE (([UserGroup] Like 'skizzle*') <> False)
 

I think...

Unfortunately this query never exists in code, such as the VBA language...

[NeoPa]

So, you run the query natively?
We're not talking about building up the SQL in code then executing it with a DoCmd.RunSQL type command?
In that case I can work with your last post.

[Sandro997]

That is correct, there is no DoCmd.RunSQL command, it is native. It's actually the record source of a combobox, in a saved query.

Question, have you been able to reproduce my problem on your own machine?

[NeoPa]

[quote=Sandro997]Question, have you been able to reproduce my problem on your own machine?/QUOTE]
No, sorry.
I've only just got the info & have life :-(

[Sandro997]

I'm using Access 2003...are you using the same version?

[NeoPa]

I'm using Access 2003...are you using the same version?

2K @work & 2K3 @home.
Do you have any reason to believe it will make a difference?
I actually do most of my working in my head so it shouldn't make too much difference ;)

[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 SetUserGroupCriteria()
3. Create a Function in one of your modules called SetUserGroupCriteria()
4. Populate that function with:

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

5. Place a breakpoint in that function to confirm it is being called, and is returning: "[Field] Like "NONEXISTENTSTRING""
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 :)