none
Using a User Defined Function in a query RRS feed

  • Question

  • I have a query that I am trying to run a function as the criteria, but I can't get it to work. Below is the Function code and below that is the SQL. I don't get an error, but I get no records. If I copy, from debug, the results of the query, I get the records that I am looking for, so I'm not sure why it is not working. Any ideas?

    Function:

    Public Function ExecMatch() As String
        Dim strLen As String
        Dim strExec As String
       
        Dim pos As Integer
         Dim Obj As AccessObject, Dbs As Object, RecSet As Recordset
         
         Set Dbs = Application.CurrentData
         strExec = " "
       
       Set RecSet = CurrentDb.OpenRecordset("tblRespExec")

      
     
      
       RecSet.MoveLast
       Debug.Print RecSet![Responsible Executive]
       RecSet.MoveFirst
       Debug.Print RecSet![Responsible Executive]
      
       Do While Not RecSet.EOF
        If RecSet![Group] = "Corp" Then
        Debug.Print RecSet![Responsible Executive], RecSet![Group]
        strExec = strExec + """" + RecSet![Responsible Executive] + """"
        strExec = strExec + " Or "
           
      
       Debug.Print strExec
       End If
       RecSet.MoveNext
       Loop
      
      pos = InStrRev(strExec, ",")
      strLen = Len(strExec)
      strExec = Left(strExec, strLen - 4)

       Debug.Print strExec
       
       
    ExecMatch = strExec
    End Function

    SQL:

    SELECT tblfindings.[Finding Open?], tblfindings.[RPM ID], tblfindings.Concern, tblfindings.[Responsible Executive]
    FROM tblfindings
    WHERE (((tblfindings.[Finding Open?])=True) AND ((tblfindings.[Responsible Executive])=ExecMatch()));


    Dean J. Waring

    Friday, July 27, 2018 6:43 PM

Answers

All replies

  • SELECT tblfindings.[Finding Open?], tblfindings.[RPM ID], tblfindings.Concern, tblfindings.[Responsible Executive]
    FROM tblfindings
    WHERE (((tblfindings.[Finding Open?])=True) AND ((tblfindings.[Responsible Executive])=ExecMatch()));

    Hi Dean,

    Your WHERE clause should read:

        WHERE [Finding Open?] = TRUE AND ([Responsible Executive] = 'aaa' OR [Responsible Executive] = 'bbb' OR …),

    so each time included the field name.

    Alternatively you could use the IN component:

        WHERE [Finding Open?] = TRUE AND [Responsible Executive] IN ('aaa','bbb',...)

    You can modify ExecMatch accordingly.

    Imb.

    Friday, July 27, 2018 7:03 PM
  • Hi Dean,

    The first thing that strikes me is that you use the + sign to link strings. In VBA you do that with the & sign. You must also take into account the fact that you use the opening and closing brackets correctly, especially if an OR operator is involved. This is what you should do first. I have not (yet) analyzed the rest of your code.

    Saturday, July 28, 2018 11:58 AM
  • As far as I can see you are attempting to return rows from tblFindings where the value at the Responsible Executive column position equals any one of the values at the Responsible Executive column position in tblRespExec in those rows where the value at the Group column position is "Corp".  To do so in the way you are attempting you should build the entire SQL statement for the query in code and concatenate the return value of the function into the string expression.  In the code you can then assign the string expression to the RecordSource property of a form or report.

    However you should be able to achieve the same result far more simply and efficiently with a JOIN:

    SELECT tblfindings.[Finding Open?], tblfindings.[RPM ID], tblfindings.Concern, tblfindings.[Responsible Executive]
    FROM tblfindings INNER JOIN tblRespExec
    ON tblfindings.[Responsible Executive] = tblRespExec.[Responsible Executive]
    WHERE tblfindings.[Finding Open?] = TRUE
    AND tblRespExec.Group = "Corp";

    Ken Sheridan, Stafford, England

    Saturday, July 28, 2018 4:58 PM
  • Not sure what the 'aaa', 'bbb' are referring to in the IN statement. What I am trying to do is find out  if the "Corp" [Responsible Executive]'s name appears in one of five Executive fields...Resp Exec, Resp Exec2, Resp Exec3...I'm trying to avoid hard coding a person's name in the query because, this kind of filter appears in numberous queries and "Execs" change often.  I want tblRespExec to be the only place where I need to update a person's name.  The goal of the ExecMatch function was to return the string that I have otherwise had to hard code into the query(i.e. "Smith" or "Jones" or "Black" or "White" or "Summer"). As it stands, each Exec could appear in one of the five places, so I have to filter for that.  The string that the function built looked good in a debug print statement.  If I copied the debug print result and pasted it into the query, it worked.  However, if I called the function from the query, it did not work.


    Dean J. Waring

    Monday, July 30, 2018 2:16 PM
  • What I am trying to do is find out  if the "Corp" [Responsible Executive]'s name appears in one of five Executive fields...Resp Exec, Resp Exec2, Resp Exec3...

    I'm sorry to say, but that is poor relational database design as it 'encodes data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    The table should be decomposed into two related tables, of which the referencing table includes a foreign key referencing the primary key of the current table, a single column for a numeric ExecID column or similar (personal names can legitimately be duplicates, so are unsuitable as keys), and possibly a further column which defines what, if anything, differentiates each of the current five columns, e.g. ExecCategoryID.

    All that is then necessary in a query is to apply a single criterion to the ExecID column in the referenced table.  This could be a parameter or it could be a JOIN criterion.  I suspect it's the latter, bu It's hard to be sure on the basis of the information available.

    Ken Sheridan, Stafford, England

    Monday, July 30, 2018 4:12 PM
  • Please use this, this should work. If you still see the same issue please let us know.

    Public Function ExecMatch() As String
        Dim strExec As String
        Dim Dbs As DAO.Database
        Dim RecSet As DAO.Recordset
        
        Set Dbs = CodeDb()
        
        Set RecSet = Dbs.OpenRecordset("tblRespExec")
        With RecSet
            If Not (.EOF And .BOF) Then
                Do While Not .EOF
                    If NZ(![Group], "") = "Corp" And NZ(![Responsible Executive], "") <> "" Then
                        If strExec = "" Then
                            strExec = "'" & NZ(![Responsible Executive], "") & "'"
                        Else
                            strExec = " OR '" & NZ(![Responsible Executive], "") & "'"
                        End If
                    End If
                    .MoveNext
                Loop
            End If
        End With
        
        Debug.Print strExec
        
        ExecMatch = strExec
        
        Set RecSet = Nothing
        Set Dbs = Nothing
    End Function
    
    SELECT tblfindings.[Finding Open?], tblfindings.[RPM ID], tblfindings.Concern, tblfindings.[Responsible Executive]
    FROM tblfindings
    WHERE tblfindings.[Finding Open?]=True AND tblfindings.[Responsible Executive]=ExecMatch();



    • Edited by almahmood Monday, July 30, 2018 4:46 PM
    Monday, July 30, 2018 4:43 PM
  • In this case, the Execs function differs depending upon whether they are primary, secondary, tertiary, fourth or fifth and need to  be tracked in such a manner. the tblRespExec just has the Exec names and their business lines and a primary key.  tblfindings has five columns depending upon what that Exec's role is on that specific issue.  The key is that the Exec can appear in one of five places and I need to be able to isolate that instance and create individual executive reports based on whether they are primary, secondary...etc......

    Why would I be able to copy and paste the result of the debug.print statement into the Exec field in the query and have it work, but I can't call the function in the criteria row of the field.  I assume that I am doing something incorrectly.


    Dean J. Waring

    Monday, July 30, 2018 4:50 PM
  • Thank you for your answer. I'm reviewing the code because it is only assigning the last Exec and the work Or to the ExecMatch string, so I will see if I can figure out why it is overwriting the others.  Again, thank you for taking the time to help.

    Dean J. Waring

    Monday, July 30, 2018 5:07 PM
  • Hi Dean,

    I thought same about why copy/paste worked. I am not sure about the reason. If you could send us the database, we can probably tell you the exact reason. However, have you tried with the code that I posted 20 mins ago?

    Thanks

    Monday, July 30, 2018 5:11 PM
  • I found the issue with the above code and addedafter the Else....strExec= strExec & "OR '"...that made the string produce this list:

    'Love' OR 'Marcuse' OR 'Singh' OR 'Webster' OR 'O'Dell' OR 'Glaessner' OR 'Guerin' OR 'Newman' OR 'Pollock' to ExecMatch....however, it also did not produce the results sought after.  I copied and pasted your results into the query criteria grid, and sure enough, it worked fine.


    Dean J. Waring

    Monday, July 30, 2018 5:17 PM
  • Yes, I did try your code and fixed it.  It too produced the same results that my code did although yours was much more succinct. 

    Dean J. Waring

    Monday, July 30, 2018 5:19 PM
  • How do I send it to you?

    Dean J. Waring

    Monday, July 30, 2018 5:40 PM
  • In this case, the Execs function differs depending upon whether they are primary, secondary, tertiary, fourth or fifth and need to  be tracked in such a manner.


    That confirms what I posted earlier.  Primary, secondary, etc are data, and by encoding those data in column headings the Information Principle is violated.  Note that even if the data are semantically meaningless, and merely represent arbitrary numbering, they are still data and fall within Codd's definition.

    These rules exist for very good reasons, and we ignore them at our peril.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, July 30, 2018 5:57 PM typo corrected.
    Monday, July 30, 2018 5:56 PM
  • You can send using any cloud like DropBox/Google Drive/OneDrive.
    • Marked as answer by DeanJW2006 Monday, July 30, 2018 6:48 PM
    Monday, July 30, 2018 6:09 PM
  • As it turns out, there was one too many spaces between the name and the ' mark.  I removed the space and your code worked as it should.....thank you for your help.

    Dean J. Waring

    Monday, July 30, 2018 6:48 PM