Multiselect Listbox

[smcgrath via AccessMonster.com]

I changed all three to multiselect listboxes and I think I figured out how to
add them to the code but I am having issues with the strWhere clause - I
think it has to do with the field types but I'm not sure. How do I change
the types?

[Status] = text
[originator] = text
[LoanTerm] = numeric


Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """"
strDoc = "RptLoanSale"

With Me.LstStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim
& ","
End If
Next
End With
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Status] IN (" & Left$(strWhere, lngLen) & ")"
End If


Dim strWhere1 As String 'String to use as WhereCondition
With Me.lstOrig
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) &
strDelim & ","
End If
Next
End With
lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[Originator] IN (" & Left$(strWhere1, lngLen) & ")"
End If

'***********
Dim strWhere2 As String 'String to use as WhereCondition
With Me.LstTerm
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & strDelim & .ItemData(varItem) &
strDelim & ","
End If
Next
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[LoanTerm] IN (" & Left$(strWhere1, lngLen) & ")"
End If


If Len(strWhere) > 0 Then strWhere = strWhere & " AND "
If Len(strWhere1) > 0 Then strWhere = strWhere & strWhere1 & " AND '"
If Len(strWhere2) > 0 Then strWhere = strWhere & strWhere2 & " AND "

'remove trailing and
If strWhere > "" Then strWhere = Left(strWhere, Len(strWhere) - 5)

Debug.Print strWhere

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, "RptLoanSale"
End If
DoCmd.OpenReport "RptLoanSale", acViewPreview, , strWhere

End Sub

 

[Stefan Hoffmann]

hi,

I changed all three to multiselect listboxes and I think I figured out how to
add them to the code but I am having issues with the strWhere clause - I
think it has to do with the field types but I'm not sure. How do I change
the types?

strDelim = """"

Maybe the cause as you're later also using the single quote as delimiter.

Use a helper function like

Public Function SQLQuote(AValue As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = ADelimiter & _
Replace(AValue, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

and rewrite your string building:

strDoc = "RptLoanSale"
With Me.LstStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim
& ","

strWhere = strWhere & SQLQuote(.ItemData(varItem)) & ", "

End If [..]
Debug.Print strWhere

What does it say?


mfG
--> stefan <--

 

[smcgrath via AccessMonster.com]

Not quite sure I understand the function part or where I should change the
string

hi,

I changed all three to multiselect listboxes and I think I figured out how to
add them to the code but I am having issues with the strWhere clause - I
think it has to do with the field types but I'm not sure. How do I change
the types?

strDelim = """"

Maybe the cause as you're later also using the single quote as delimiter.

Use a helper function like

Public Function SQLQuote(AValue As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = ADelimiter & _
Replace(AValue, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

and rewrite your string building:

strDoc = "RptLoanSale"
With Me.LstStatus
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim
& ","

strWhere = strWhere & SQLQuote(.ItemData(varItem)) & ", "

End If [..]
Debug.Print strWhere

What does it say?

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Not quite sure I understand the function part or where I should change the
string

Paste the function into a standard module. The function encloses strings
with (single) quotes and doubles them if found in the content, e.g.
d'Arcy must be used as 'd''Arcy' otherwise the Expression Service/Jet
could not parse the finale SQL statement.

Use the function for building your WHERE clauses in your code. Drop the
usage of strDelim.



mfG
--> stefan <--