Problems with Recordsets when items contain quotes.

[caddcop]

I have a listbox that reads values from a query. At least one item it returns contains double quotes. I cannot be sure that others won't contain single quotes. And there is also the possibility a record might contain both.

my code is:

rs.FindFirst "[Name] = " & Chr(34) & Me!
[List_AssignedStaff] & Chr(34)

Is there another way of writting this such that single or double quotes will not cause it to fail?

[orange]

Try

rs.FindFirst "[Name] = '" & Me!
[List_AssignedStaff] & "'"

[RuralGuy]

Assuming the [Name] field *never* has a double quote in it then this should work:
rs.FindFirst "[Name] = " & Chr(34) & Replace(Me![List_AssignedStaff],'"','') & Chr(34)
I might have the syntax a little wrong so play with it.

[caddcop]

I'm having trouble reading orange's response.

What I am doing now is this:

If InStr(1, Me!
[List_AssignedStaff], Chr(34)) Then
        rs.FindFirst "[Name] = " & Chr(39) & Me!
[List_AssignedStaff] & Chr(39)
    Else
        rs.FindFirst "[Name] = " & Chr(34) & Me!
[List_AssignedStaff] & Chr(34)
    End If

Which only works if there is only " or only ' and not both. Currently, that seems to be true. I may add a message box that warns to not use both.

[RuralGuy]

Did you try my suggestion?

[caddcop]

Currently, it is a name record with double quotes that alerted me to the problem. So I developed my solution. For clarity, I should use:

If InStr(1, Me!
[List_AssignedStaff], Chr(34)) > 0 Then
        rs.FindFirst "[Name] = " & Chr(39) & Me!
[List_AssignedStaff] & Chr(39)
Else
        rs.FindFirst "[Name] = " & Chr(34) & Me!
[List_AssignedStaff] & Chr(34)
End If

[orange]

So you have it working, great.

Are you certain that the incoming name contains a single " ?
Often, to make a single ", you must use a double "" (2 double quotes)

I set up a small test to see if I could make it work (it's been a while)
I could not make a variable with a single contained " (chr(34))

Sub testQuote()
Dim list_assignedstaff As String
list_assisgnedstaff = "O""Leary" ' second version to try "O'Leary"
Debug.Print list_assisgnedstaff
Debug.Print InStr(list_assisgnedstaff, Chr(34))
If InStr(list_assisgnedstaff, """") > 0 Then

Debug.Print "[Name] = " & Replace(list_assisgnedstaff, Chr(34), Chr(39))
Else
Debug.Print "[Name] = " & list_assisgnedstaff
End If
End Sub