Coding issue/error results

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

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
 
Keith,

If I understand correctly, you want to find tellers at

a Bank AND a Branch AND (one or more optional OR descriptior conditions).

I tested this up to the ADO recordset statement. Watch for line wrap.....

Try this:

'---- Code Start -------
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 strSQL As String
Dim strWhere As String
Dim strReturn As String

strWhere = " where IMBank = " & intIMBk & " and IMBranch = " & intIMBranch & " AND (IMItemInfo = '" & strDesc & "'"

'now check to see if the optional parameters are missing
If Not IsMissing(strDesc1) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc1 & "'"
End If
If Not IsMissing(strDesc2) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc2 & "'"
End If
If Not IsMissing(strDesc3) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc3 & "'"
End If
If Not IsMissing(strDesc4) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc4 & "'"
End If
If Not IsMissing(strDesc5) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc5 & "'"
End If
If Not IsMissing(strDesc6) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc6 & "'"
End If
If Not IsMissing(strDesc7) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc7 & "'"
End If
If Not IsMissing(strDesc8) Then
strWhere = strWhere & " Or IMItemInfo = '" & strDesc8 & "'"
End If
strWhere = strWhere & ")"

strSQL = "Select IMTeller From qryCBListing" & strWhere

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
' note that rst is only used in the next line
With rst
If .RecordCount >= 1 Then
Do While Not .EOF
strReturn = strReturn & !IMTeller & ","
.MoveNext
Loop
End If
End With

' set up the default return
fReturnTellers = "Not Found"
' uncomment the next line and comment the previous line to return nothing
'fReturnTellers = ""

' strip the last comma
If Len(strReturn) > 1 Then
fReturnTellers = Left(strReturn, Len(strReturn) - 1)
End If

End Function
'---- Code end -----


HTH
Steve
 
Back
Top