Query criteria as function

[Sandro997]

SELECT * FROM tblLabor WHERE tblLabor.UserGr oup=SetUserGrou pCriteria()

I have something similar to this in a query that I'm using. I want SetUserGroupCri teria() to return something along the lines of
[Like "CA*" and Not like "RN*"] Unfortunately SetUserGroupCri teria() 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.UserGr oup=SetUserGrou pCriteria()

I have something similar to this in a query that I'm using. I want SetUserGroupCri teria() to return something along the lines of
[Like "CA*" and Not like "RN*"] Unfortunately SetUserGroupCri teria() 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 SetUserGroupCri teria 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 SetUserGroupCri teria()?

Thx

[NeoPa]

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

[Sandro997]

Hi Sandro997,
Is SetUserGroupCri teria 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 SetUserGroupCri teria()?

Thx

Ya, this function is used all over the place.

Public Function SetUserGroupCri teria() As String
Select Case GetUserGroupByU serID(fGetUserI D())
Case "UserType1" SetUserGroupLik eCriteria = "Not like ""CA*"" and Not like ""RN*"""
Case "UserType2" SetUserGroupLik eCriteria = "Not like ""LS*"" and Not like ""FM*"""
Case "UserType3" SetUserGroupLik eCriteria = "Not like ""FM*"""
Case "UserType4" SetUserGroupLik eCriteria = "Like ""*"""
Case "UserType5" SetUserGroupLik eCriteria = "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 SetUserGroupCri teria()
and SetUserGroupCri teria() 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 qryGetLaborStuf f, and I am working the SQL through this medium, if I convert to design mode this turns into
Field: SetUserGroupCri teria()
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 misunderstandin g 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 misunderstandin g 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 ;)