[mustish1]

Below is the function which receive the clt_ref_no in AcctNum. I dont want to disturb that function. What i need is within that function it calls two new functions and pass the same AcctNum into new functions. Query will be little change for both functions. For the first function

Select LongStr from dbtrudf du left outer join debt as d on (d.debt_id = du.debtor_id,0) where label = 'BankruptcyChapter'
and clt_ref_no = '5499440916866424'

For the second function
Select LongStr from dbtrudf du left outer join debt as d on (d.debt_id = du.debtor_id,0) where label = 'CaseNumber'
and clt_ref_no = '5499440916866424'

At end in both new functions it returns the value into new variables. I mean in both new functions i need value from field LongStr. The two functions basically give me the value if Label="CaseNumber" or Label="BankruptcyChapter". so the new two function holds value for CaseNumber and BankruptcyChapter from field LongStr

Public Function GetBankruptcyDate(ByVal AcctNum As Variant) As String
Dim RS As New ADODB.Recordset
Dim DBConn As ADODB.Connection
Dim SQL As String, strDate As String

AcctNum = Trim(AcctNum)

SQL = "set temporary option user_estimates = 'enabled'; " & _
" Select date1 from dbtrudf du " & _
" left outer join debt as d on (d.debt_id = du.debtor_id,0) " & _
" where label = 'FileDate' " & _
" and clt_ref_no = '" & Trim(AcctNum) & "'"
Debug.Print SQL
Set DBConn = CommonFunctions.GetDBConn
Set RS = DBConn.Execute(SQL)

If Not RS.EOF And Not IsNull(RS!DATE1) Then
strDate = Format$(CStr(RS!DATE1), "yyyymmdd")
Else
MsgBox AcctNum & " is an 03A with no Bankruptcy date!"
End If
GetBankruptcyDate = strDate
End Function

Thanks.