Form Filter with apostrophe in filter results

[gcarpenter]

I use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string. Please help.

Private Sub FilterDesc_Click()
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.ListCarrier.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Carrier"
Exit Sub
End If
'add selected values to string
Set ctl = Me.ListCarrier
For Each varItem In ctl.ItemsSelected
' strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptPolyRezCost-Alt", acViewReport, , "Desc IN(" & strWhere & ")"

DoCmd.Close acForm, "PolyRezCostFilterEquip"
End Sub

 

[jdraw]

You could try doubling the apostrophe

eg

If Instr(myField,"'") > 0 then
MyField = Replace(MyField,"[COLOR="Red"]'[/COLOR]","[COLOR="Red"]''[/COLOR]")
..

 

[gcarpenter]

Where does that fit into my code?

 

[jdraw]

Which field has the apostrophe that's causing the error?

 

[gcarpenter]

This is the field with the apostrophe in it. It is in red.

DoCmd.OpenReport "rptPolyRezCost-Alt", acViewReport, , "Desc IN(" & strWhere & ")"

 

[pr2-eugin]

Try this code.

Private Sub FilterDesc_Click()
    Dim strWhere As String, ctl As Control, varItem As Variant
    
    [COLOR=Green]'make sure a selection has been made[/COLOR]
    If Me.ListCarrier.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 Carrier"
        Exit Sub
    End If
    
    [COLOR=Green]'add selected values to string[/COLOR]
    Set ctl = Me.ListCarrier
    For Each varItem In ctl.ItemsSelected
        [COLOR=Green]' strWhere = strWhere & ctl.ItemData(varItem) & ","
        'Use this line if your value is text[/COLOR]
        strWhere = strWhere [COLOR=Red][B]& Chr(34) &[/B][/COLOR] ctl.ItemData(varItem) [COLOR=Red][B]& Chr(34) &[/B][/COLOR] ","
    Next varItem
        
    [COLOR=Green]'trim trailing comma[/COLOR]
    strWhere = Left(strWhere, Len(strWhere) - 1)
    [COLOR=Green]'open the report, restricted to the selected items[/COLOR]
    DoCmd.OpenReport "rptPolyRezCost-Alt", acViewReport, , "Desc IN(" & strWhere & ")"

    DoCmd.Close acForm, "PolyRezCostFilterEquip"
End Sub

 

[gcarpenter]

pr2, worked like a champ, I cannot thank you enough.

 

[pr2-eugin]

pr2, worked like a champ, I cannot thank you enough.

You can, by clicking the 'Thanks' button ! Lol. Am glad to help, Good luck !