[RESOLVED] Function question

[nadia]

Can a function return more than 1 result?

I call the following function with this:
Call claimNumberSuffix(LCase("mySystem"), UCase("myClaimNumber%"))
Debug.Print Call claimNumberSuffix(LCase("CLMSYS03"), UCase("PLOAUTOMATION_UMRCLAIM1%"))
Debug.Print submitClaimNumber, claimSuffixNumber, claimSuffixNumber

What displays is the value for submitClaimNumber 2 times.
I looked online and didn't see anythign that specifically says functions.can only return 1 value.

I may have another way to work around this.
Thanks

Function claimNumberSuffix(clmSystem As String, ByVal myClaim As String) As String


'this functions makes a connection to the claims system and queries the claim table to
'return the networx number

'Create DB connection
Dim myDb2 As New ADODB.Connection
'Create a new recordset
Dim rsTemp2 As New ADODB.Recordset
'Open the DB using the clmSystem argument as a string
myDb2.Open "DSN=claimsDB;SERVER=CDCNSYS1;PWD=" & clmSystem & "_repricer;UID=" & clmSystem & "_repricer;"
'Example of the open statement w/out an arugment

'SQL statement with the argument
claimSuffixQuery = "select max(submitterclaimnumber)as scn from clmclaims where receiveddate > (sysdate-1) and claimstatus not in (1007) and upper(submitterclaimnumber) like '" & UCase(myClaim) & "'"
'SQL statement with string
_UMRCLAIM2'"
'open recordset
rsTemp2.Open claimSuffixQuery, myDb2
'assign the recordset result to a variable
submitClaimNumber = rsTemp2("scn")
'clean-up
rsTemp2.Close
myDb2.Close
Set myDb2 = Nothing

'Determines the position of the - in the submitterclaimnumber
'- value of 0 means that the claim does not have a suffix
posn = InStr(1, submitClaimNumber, "-", vbBinaryCompare)

If posn = 0 Then
claimSuffixNumber= 0

Else
claimSuffixNumber= Mid(submitClaimNumber, myPos + 1)
End If

End Function

[nadia]

n/m...figured out what was wrong.

[Xium]

functions always return single value . for multiple values u can use procedures. u can pass by reference parameters to procedure and procedure will return resluts in those parameters which will effect ur actuall variable