Help calling function from query

[LakotaMan]

Hello all,

I think I’m going nuts. I am trying to use a function to provide the criteria for a query and am getting no results.

Here is the SQL:

SELECT tblComplaints.ComplaintInitiator, tblComplaints.ComplaintID, tblComplaints.ComplaintNum, tblComplaints.CustomerName, tblComplaints.ComplaintRcdDate, tblComplaints.DateSentToRespondees, tblComplaints.DateReSentToRespondees, tblComplaints.AreaId
FROM tblComplaints
WHERE (((tblComplaints.AreaId)=GetAreas()) AND ((tblComplaints.Status)="open"));

Here is the function:

Public Function GetAreas()
    
    Dim cnn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
    Dim strCriteria As String
    
   'Get user login Info
    strUserLogin = fWin2KUserName()
    UserName = DLookup("UserName", "qrySecurityInfo", "LogonID= '" & strUserLogin & "'")
    
    Set cnn = CurrentProject.Connection
    
    strSQL = "SELECT LogonID, AreaID "
    strSQL = strSQL & "FROM qryUserAreas "
    strSQL = strSQL & "WHERE LogonID = " & "'" & strUserLogin & "'"

    rs.Open strSQL, cnn    
   
 If Not rs.EOF Then
        rs.MoveFirst        
        'Begin Loop to generate criteria of multiple areas per person            
            strCriteria = " IN ("    
            Do While Not rs.EOF
                strCriteria = strCriteria & rs!AreaId & ","
                rs.MoveNext
            Loop    
        rs.Close        
        strCriteria = strCriteria & ")"
    Else
    End If
    
    If strCriteria = "" Then
        GetAreas = ""
    Else
        GetAreas = strCriteria
    End If
End Function

I have used stops and message boxes to determine the function is producing the correct criteria, as per this example:

IN (26, 10, 41,)

But when the query runs, I get no records. I don’t do this very often, so I must be doing something wrong, but for the life of me I can’t see it.
Any help will, as always, be greatly appreciated.
LM

 

[dhookom]

You are going nuts because it won't work as you want it to. You can't use a function that returns the operator (IN). You can use a little DAO code to change the SQL of the saved query like:

Dim strSQL as String
strSQL = "SELECT ComplaintInitiator, ComplaintID, ComplaintNum, " & _
   "CustomerName, ComplaintRcdDate, DateSentToRespondees, " & _
   "DateReSentToRespondees, AreaId " & _
   "FROM tblComplaints " & _
   "WHERE AreaId " & GetAreas() & _
   " AND Status='open';"
Currentdb.QueryDefs("YourQueryName").SQL = strSQL

Your GetAreas() function should also remove the final comma.


Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Duane,
Thanks for the input --can I use what you've written for ADO?
LM

 

[dhookom]

I don't generally use ADO in Access with JET. You should be able to set a reference to an ADO library that allows you to set the SQL property of a saved query.

Duane
Hook'D on Access
MS Access MVP