By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

Custom functions in the criteria of a query

P: 5
I am trying to build a query which will uses a custom function as criteria. I have searched the forums and found nothing, so either it can't be done or I am just missing something simple.

The function code returns the information I want, but it doesn't come back to the query properly.

my query (which returns no records)
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT tblpermit.permitlevel, tblpermit.permitusername
  3. FROM tblpermit
  4. WHERE (((tblpermit.permitlevel)=funGetPermitLevel()));
  5.  
the function: funGetPermitLevel
Expand|Select|Wrap|Line Numbers
  1. Public Function funGetPermitLevel() As String
  2.     Dim PLevel As String
  3.     Dim db As DAO.Database
  4.     Dim rs As DAO.Recordset
  5.  
  6.     sql = "Select * From tblpermit Where permituserid = '" & Environ("username") & "'   "
  7.     Set db = CurrentDb
  8.     Set rs = db.OpenRecordset(sql, dbOpenSnapshot, dbReadOnly)
  9.  
  10.     If rs!permitLevel = "Admins" Then
  11.         PLevel = "L1Review"
  12.     Else
  13.         PLevel = "'L1Review or L2Review"
  14.     End If
  15.     funGetPermitLevel = PLevel
  16. End Function
  17.  
The function is getting the level of permissions for the user based on their user ID which is set in an environment label. Within the function, rs!permitLevel returns "Admins" and the funGetPermitLevel gets set to "L1Review".
How do use this string as criteria in a query.

We are trying to limit what the users see without having to create different queries for each group.

Thanks in advance for your help.

Steve
Sep 3 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 112
What does it return in the query?
Sep 3 '08 #2

P: 5
What does it return in the query?
Nothing...Just blank. No records.
Sep 4 '08 #3

P: 5
Ok, figured it out... It appears that Access does not allow functions in the criteria. So I switched it around a bit and put the function in the select area and had the fuction return a "Y" or a "N" and then in the criteria put ia "Y" or "N" to only return the records that I wanted.

Here is the query:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblpermit.permitlevel, tblpermit.permitusername
  2. FROM tblpermit
  3. WHERE (((funGetPermitLevel([permitlevel]))="Y"));
  4.  
This is the function:
Expand|Select|Wrap|Line Numbers
  1. Public Function funGetPermitLevel(level As String) As String
  2.     Dim db As DAO.Database
  3.     Dim rs As DAO.Recordset
  4.     Dim result As String
  5.  
  6.     sql = "Select * From tblpermit Where permituserid = '" & Environ("username") & "'   "
  7.     Set db = CurrentDb
  8.     Set rs = db.OpenRecordset(sql, dbOpenSnapshot, dbReadOnly)
  9.  
  10.     If rs!permitlevel = "Admins" Then
  11.         result = "Y"
  12.     ElseIf rs!permitlevel = "L1Super" And (level = "'L1Review' or level= 'L1Super'") Then
  13.         result = "Y"
  14.     ElseIf rs!permitlevel = "L1Review" And level = "L1Review" Then
  15.         result = "Y"
  16.     ElseIf rs!permitlevel = "L2Review" And level = "L2Review" Then
  17.         result = "Y"
  18.     Else
  19.         result = "N"
  20.     End If
  21.  
  22.     funGetPermitLevel = result
  23. End Function
  24.  
Thanks to all who looked into this.
Sep 5 '08 #4

NeoPa
Expert Mod 15k+
P: 31,489
An interesting question and an interesting solution Steve :)

Welcome to Bytes!
Sep 7 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi. As a matter of interest there is a simpler alternative to your use of the DAO recordset variable (which, by the way, you are not explictly closing after use). It's the use of the in-built DLookup function.
Expand|Select|Wrap|Line Numbers
  1. Public Function funGetPermitLevel(level As String) As String
  2.     Dim result As String
  3.     Dim strPermitLevel as String
  4.     strPermitLevel  = DLookup("[PermitLevel]", "[tblPermit]", "[permituserid] = '" & Environ("username") & "')"
  5.  
  6.     If strPermitLevel = "Admins" Then
  7.         result = "Y"
  8.     ElseIf strPermitLevel = "L1Super" And (level = "'L1Review' or level= 'L1Super'") Then
  9.         result = "Y"
  10.     ElseIf strPermitLevel = "L1Review" And level = "L1Review" Then
  11.         result = "Y"
  12.     ElseIf strPermitLevel = "L2Review" And level = "L2Review" Then
  13.         result = "Y"
  14.     Else
  15.         result = "N"
  16.     End If
  17.  
  18.     funGetPermitLevel = result
  19. End Function
  20.  
-Stewart
Sep 8 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.