CODEWITHSUNDEEP
vba
Ana Kelly
Ana Kelly

Reputation: 15

How can I return the values of specific integers?

I would like to know how to return the value of both intcon and intRed in my function:

Function CountInterface(parI As String, parC As String) As String
' mes variables
    Dim strSQLQueryCountingI As String
    Dim intCon As Integer
    Dim intRed As Integer
    Dim rstCountInt As DAO.Recordset
    
    strSQLQueryCountInterface = "SELECT " _
                                & "CcProd.F_Cpr," _
                                & "CcProd.F_Int," _
                                & "Sum(CcProd.F_Re) AS Total, " _
                                & "Sum(CcProd.F_Con) As ConPost " _
                            & "FROM " _
                                & "CcProd " _
                            & "GROUP BY " _
                                 & "CcProd.F_Cpr, " _
                                & "CcProd.F_Int" _
                            & "HAVING " _
                                & "(((CcProd.F_Int) Like '" & parI & "')" _
                                & " AND ((CcProd.F_Cpr) Like '" & parC & "' )" _
                                & ") "
                               
   
     Set rstCountInt = CurrentDb.OpenRecordset(strSQLQueryCountInterface, dbOpenDynaset)
        
            intCon = CInt(rstCountInt !Connected_Post)
            Debug.Print intCon
        
            intRed = CInt(rstCountInterface!Total_Post)
            Debug.Print intRed

End Function

Upvotes: 1

Views: 85

Answers (2)

norie
norie

Reputation: 9857

You could return them as an array.

Function CountInterface(parInterface As String, parCcProductId As String) As Variant
' mes variables
Dim strSQLQueryCountInterface As String
Dim intConnect As Integer
Dim intRegistered As Integer
Dim rstCountInterface As DAO.Recordset

    strSQLQueryCountInterface = "SELECT " _
                                & "CcProductid_details.F_Cc_Product_Id," _
                                & "CcProductid_details.F_Interface," _
                                & "Sum(CcProductid_details.F_Registered) AS Total_Post, " _
                                & "Sum(CcProductid_details.F_Connected) As Connected_Post " _
                                & "FROM " _
                                & "CcProductid_details " _
                                & "GROUP BY " _
                                & "CcProductid_details.F_Cc_Product_Id, " _
                                & "CcProductid_details.F_Interface " _
                                & "HAVING " _
                                & "(((CcProductid_details.F_Interface) Like '" & parInterface & "')" _
                                & " AND ((CcProductid_details.F_Cc_Product_Id) Like '" & parCcProductId & "' )" _
                                & ") "


    Set rstCountInterface = CurrentDb.OpenRecordset(strSQLQueryCountInterface, dbOpenDynaset)

    intConnect = CInt(rstCountInterface!Connected_Post)
    Debug.Print intConnect

    intRegistered = CInt(rstCountInterface!Total_Post)
    Debug.Print intRegistered

    CountInterface = Array(intConnect, intRegistered)

End Function

Or a dictionary.

Function CountInterface(parInterface As String, parCcProductId As String) As Variant
' mes variables
Dim strSQLQueryCountInterface As String
Dim intConnect As Integer
Dim intRegistered As Integer
Dim rstCountInterface As DAO.Recordset
Dim dicCounts As Object

    strSQLQueryCountInterface = "SELECT " _
                                & "CcProductid_details.F_Cc_Product_Id," _
                                & "CcProductid_details.F_Interface," _
                                & "Sum(CcProductid_details.F_Registered) AS Total_Post, " _
                                & "Sum(CcProductid_details.F_Connected) As Connected_Post " _
                                & "FROM " _
                                & "CcProductid_details " _
                                & "GROUP BY " _
                                & "CcProductid_details.F_Cc_Product_Id, " _
                                & "CcProductid_details.F_Interface " _
                                & "HAVING " _
                                & "(((CcProductid_details.F_Interface) Like '" & parInterface & "')" _
                                & " AND ((CcProductid_details.F_Cc_Product_Id) Like '" & parCcProductId & "' )" _
                                & ") "


    Set rstCountInterface = CurrentDb.OpenRecordset(strSQLQueryCountInterface, dbOpenDynaset)

    intConnect = CInt(rstCountInterface!Connected_Post)

    intRegistered = CInt(rstCountInterface!Total_Post)

    Set dicCounts = CreateObject("Scripting.Dictionary")

    dicCounts("Connect") = intConnect
    dicCounts("Registered") = intRegistered

    Set CountInterface = dicCounts

End Function

Upvotes: 2

Kostas K.
Kostas K.

Reputation: 8518

You could use a custom Type object. For example:

The Type:

Public Type InterfaceType
    Connected As Integer
    Registered As Integer
End Type

Your method returning the above type:

Function ReturnInterfaceType() As InterfaceType

    Dim t As InterfaceType
    
    t.Connected = 365
    t.Registered = 400
    
    ReturnInterfaceType = t
End Function

To call it:

Sub T()

    Dim t As InterfaceType
    
    t = ReturnInterfaceType()
    
    Debug.Print t.Connected
    Debug.Print t.Registered
    
    '365
    '400
End Sub

To returnByRef, you need to update the method signature to accept the two variables. Since they're passed by reference, the function can change their values.

For example:

Function CountInterface(ByVal parInterface As String, _
                        ByVal parCcProductId As String, _
                        ByRef outConnect As Integer, _
                        ByRef outRegistered As Integer) As String

    '...
    
    outConnect = 365
    outRegistered = 400
    
End Function

To call it:

Sub T()

    Dim intConnect As Integer, intRegistered As Integer, s As String
    
    s = CountInterface("a", "b", intConnect, intRegistered)
    
    Debug.Print intConnect
    Debug.Print intRegistered
    
    '365
    '400
End Sub

Upvotes: 4

Related Questions