hi all
is it possible to return 2 values in a function
iam calling query_execute function
xlSht.Cells(7, Cols) = Query_Execute1(q, c)
Public Function Query_Execute1(q, c)
Dim Rs As Recordset
Dim sSql As String
Set db = CurrentDb()
sSql = "SELECT Sum(txbal.DEBIT_NO) AS SumOfDEBIT_NO, Sum(txbal.DEBIT_AMOUNT) AS SumOfDEBIT_AMOUNT"
sSql = sSql + " FROM (txbal INNER JOIN TRXNTYPE ON txbal.TRXN_CODE = TRXNTYPE.TRXNCODE) INNER JOIN CARD_TYPE ON txbal.CARD_TYPE = CARD_TYPE.CARD_TYPE"
sSql = sSql + " WHERE (((CARD_TYPE.PROVIDER)='visa') AND ((CARD_TYPE.TYPE)='classic') AND ((txbal.TRXN_CODE)= 40) and ((txbal.TRXN_TYPE)='D E B I T S' Or (txbal.TRXN_TYPE)='C R E D I T S') AND ((CARD_TYPE.PRODUCT) = '" & q & "' ));"
MsgBox sSql
Set Rs = db.OpenRecordset(sSql)
Query_Execute1(q, c) = Rs.Fields(0)
End Function
i need to return rs.fields(0) and rs.fields(1)
is it possible or not
Hi
Four things
1. Does this functionn actualy run ? I think this
Query_Execute1(q, c) = Rs.Fields(0)
should be
Query_Execute1 = Rs.Fields(0)
or more simply
Query_Execute1 = Rs(0)
2. Argument c is not used in the function (unless I've missed it)
3. I would use an ampersand (&) for string concatenation not + which will add numbers.
4. To my knowlege a function only return one value. It's also good practice to declare the function and argument types ie
Public Function Query_Execute1(ByVal q as String, ByVal c as string) as Long
You do not say how you want to use these two 'values' but if you want to add the two together in a cell then
Query_Execute1 = Rs(0) + Rs(1)
If you want to display the two values in one cell then declare the function as string and this
Query_Execute1 = Rs(0) & " " & Rs(1)
will display the values with a space.
Only guessing here but any good??
Failing this you can pass then back to the calling code as arguments (ByRef) but then you can use a sub to do this instead of a function (unless you use the function to verify that the query has executed - but that is another question/technique).
MTB
145
