Returning two database fields from a single function call

[chandru8]

hi all
is it possible to return 2 values in a function

iam calling query_execute function
[code=vb]
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_AMOU NT"
sSql = sSql + " FROM (txbal INNER JOIN TRXNTYPE ON txbal.TRXN_CODE = TRXNTYPE.TRXNCO DE) INNER JOIN CARD_TYPE ON txbal.CARD_TYPE = CARD_TYPE.CARD_ TYPE"
sSql = sSql + " WHERE (((CARD_TYPE.PR OVIDER)='visa') AND ((CARD_TYPE.TYP E)='classic') AND ((txbal.TRXN_CO DE)= 40) and ((txbal.TRXN_TY PE)='D E B I T S' Or (txbal.TRXN_TYP E)='C R E D I T S') AND ((CARD_TYPE.PRO DUCT) = '" & q & "' ));"
MsgBox sSql
Set Rs = db.OpenRecordse t(sSql)

Query_Execute1( q, c) = Rs.Fields(0)

End Function
[/code]
i need to return rs.fields(0) and rs.fields(1)

is it possible or not

[MikeTheBike]

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_AMOU NT"
sSql = sSql + " FROM (txbal INNER JOIN TRXNTYPE ON txbal.TRXN_CODE = TRXNTYPE.TRXNCO DE) INNER JOIN CARD_TYPE ON txbal.CARD_TYPE = CARD_TYPE.CARD_ TYPE"
sSql = sSql + " WHERE (((CARD_TYPE.PR OVIDER)='visa') AND ((CARD_TYPE.TYP E)='classic') AND ((txbal.TRXN_CO DE)= 40) and ((txbal.TRXN_TY PE)='D E B I T S' Or (txbal.TRXN_TYP E)='C R E D I T S') AND ((CARD_TYPE.PRO DUCT) = '" & q & "' ));"
MsgBox sSql
Set Rs = db.OpenRecordse t(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

[Killer42]

Ignore me. Just registering an interest, as I'd like to see where this thread goes.

[pbmods]

Heya, Chandru.

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

Please use CODE tags when posting source code:

[CODE=vb]
VB code goes here.
[/CODE]