Report issue/coding issues

Report issue/coding issues

Post by Keit » Wed, 05 May 2004 21:45:02


Hi all;

I am working on a report based on a query. The text box
in the report field calls a function to retrieve teller
numbers from the query based on a specific case. One of
the text boxes needs to retrieve teller numbers for
several different issues. I'm attempting to use the
Optional argument in the function call but I get errors
in all my columns afterward. I have pasted the fuction
call from the report column and then the code from the
function.

Any suggestions??? Thanks.
-------------------------------------------
(-All of the other columns use the 1st three arguments.)
=fReturnTellers([IMBank],[IMBranch],"Other","Cash","Cash-
In Transit","Chk. Reorder Form","General Ledger
Debit","InterChange","Loan Payment w/ARGO","Savings Bond
(s)","Utility Payment")

Public Function fReturnTellers(intIMBk As Integer,
intIMBranch As Integer, strDesc As String, Optional
strDesc1 As String, Optional strDesc2 As String, Optional
strDesc3 As String, Optional strDesc4 As String, Optional
strDesc5 As String, Optional strDesc6 As String, Optional
strDesc7 As String, Optional strDesc8 As String) As String

Dim rst As New ADODB.Recordset
Dim intI As Integer
Dim strSQL As String
Dim strWhere As String 'These variables are used to
Dim strItem As String 'shorten the SQL statement with
Dim strItem1 As String 'the Optional arguments.
Dim strItem2 As String
Dim strItem3 As String
Dim strItem4 As String
Dim strItem5 As String
Dim strItem6 As String
Dim strItem7 As String
Dim strItem8 As String
Dim strReturn As String

strWhere = "(IMBank = " & intIMBk & " and IMBranch = " &
intIMBranch & " "
strItem = " and IMItemInfo = '" & strDesc & "') Or "
strItem1 = " and IMItemInfo = '" & strDesc1 & "') Or "
strItem2 = " and IMItemInfo = '" & strDesc2 & "') Or "
strItem3 = " and IMItemInfo = '" & strDesc3 & "') Or "
strItem4 = " and IMItemInfo = '" & strDesc4 & "') Or "
strItem5 = " and IMItemInfo = '" & strDesc5 & "') Or "
strItem6 = " and IMItemInfo = '" & strDesc6 & "') Or "
strItem7 = " and IMItemInfo = '" & strDesc7 & "') Or "
strItem8 = " and IMItemInfo = '" & strDesc8 & "')"

'If Len(strDesc1) > 1 Then
' strSQL = "where " & strWhere & strItem & strWhere &
strItem1 & strWhere & strItem2 & strWhere & strItem3 &
strWhere & strItem4 & strWhere & strItem5 & strWhere And
strItem6 & strWhere & strItem7 & strWhere & strItem8
'Else
strSQL = "where IMBank = " & intIMBk & " and IMBranch
= " & intIMBranch & " and IMItemInfo = '" & strDesc & "'"
'End If


rst.Open "select IMTeller from qryCBListing " & strSQL,
CurrentProject.Connection, adOpenKeyset, adLockReadOnly

With rst
If rst.RecordCount >= 1 Then
Do While Not rst.EOF
strReturn = strReturn & rst!IMTeller & ","
.MoveNext
Loop
End If
End With

If Len(strReturn) > 1 Then
strReturn = Left(strReturn, Len(strReturn) - 1)
End If

fReturnTellers = strReturn
strDesc1 = Nul
strDesc2 = Nul
strDesc3 = Nul
strDesc4 = Nul
strDesc5 = Nul
strDesc6 = Nul
strDesc7 = Nul
strDesc8 = Nul

End Function