Using apostrophes in filters (VB6/Access 2K)

Topic

New Reply

In my VB application I’m taking the recordset and doing a filter on it to get some information. This works fine until it comes across a string value with an apostrophe. I know why this is occuring, but how do I code around that?

Reply | Quote

Replies

You can build your string as follow:

strCriteria = "[MyField] = " & Chr(34) & "Some's" & Chr(34)

Reply | Quote

Thanks Francois. This should have worked. When I looked at it in the debug window the syntax looked absolutely correct. Can you see what is wrong in the FILTER = section.

Private Sub cmdSave_Click()

”’Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

On Error GoTo ErrorHandler

‘****************************************************************
‘Section to handle Diagnosis if provided
‘****************************************************************

lblStatus.Caption = “Processing . . .”

Screen.MousePointer = vbHourglass

Me.Refresh

If Not (cboDiag.Text = “” Or IsNull(cboDiag.Text)) Then

cn.Open sConnString

With rs
.Source = “SELECT Diagnosis FROM tblDiagnosis ORDER BY Diagnosis”
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockPessimistic
End With

rs.Open , cn, , , adCmdText

Debug.Print HandleQuotes(cboDiag.Text, “‘”)

‘ rs.Filter = “Diagnosis = ‘” & cboDiag.Text & “‘”

rs.Filter = “Diagnosis = ” & Chr(34) & cboDiag.Text & Chr(34)

If rs.RecordCount = 0 Then

With rs

.Filter = adFilterNone
.AddNew
!Diagnosis = cboDiag.Text
.Update
.Requery

End With

‘Call RefreshDiag

‘ rs.Filter = adFilterNone

End If ‘rs.RecordCount = 0 Then

End If ‘Not (cboDiag.Text = “” Or IsNull(cboDiag.Text)) Then

rs.Filter = adFilterNone

‘****************************************************************
‘Record Data
‘****************************************************************

If cn.State = 1 Then
cn.Close
End If

”’Set cn = Nothing

If bUpdateMode = False Then
Call SaveFormData
Else
Call UpdateFormData
End If

Exit_cmdSave_Click:

Set rs = Nothing

If cn.State = 1 Then
cn.Close
End If

cmdSave.Enabled = False
cmdDelete.Enabled = True
bIsDirty = False

lblStatus.Caption = “Ready”

Screen.MousePointer = vbNormal

Exit Sub

ErrorHandler:

MsgBox “Error #” & Err.Number & ” ” & Err.Description, vbCritical, “Error cmdSave_Click”

Resume Exit_cmdSave_Click

End Sub

Reply | Quote

I’m not familiar with ado but I would change the code to

With rs
.Source = "SELECT Diagnosis FROM tblDiagnosis WHERE Diagnosis = " & Chr(34) & cboDiag.Text & Chr(34)
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockPessimistic
End With
rs.Open , cn, , , adCmdText
If rs.RecordCount = 0 Then
...

Reply | Quote

The way I’m using the data from the recordset requires I use the filter. I mean I can use another recordset but it would mean making another round trip to the database.

Reply | Quote

Hi Mike,
Does it work if you use:
rs.Filter = “Diagnosis = “”” & cboDiag.Text & “””
instead?
(Incidentally, I’d probably store cboDiag.Text in a string variable rather than referring to it repeatedly.)

Reply | Quote

It still had the problem with the apostrophe in the string. If I were to do a debug.print, copy and paste the value defining that filter into an Access query on the back end, it pulls it up just fine. The syntax looks correct with the code you gave, it is just being persistent about that apostrophe. . .

Reply | Quote

Are you getting an error message of any sort or does it simply not retrieve any records?

Reply | Quote

Hi Rory, Yes I am getting an error stating that the arguments are of the wrong type, are out of acceptable range, or are in conflict with one another (Error 3001).

Reply | Quote

Edited by rory on 11-Feb-02 08:34.

Hi Mike,
I think you’ve got two options. Either run some code to double the apostrophe in the string you’re searching for (which should cause ADO to treat it as a literal) or use the following syntax:
rs.filter “Diagnosis = #” & cboForgotTheName.Text & “#”
The latter will handle the eventuality that there’s more than one apostrophe in the text (which the former won’t) but has the drawback that there can’t be a ‘#’ sign in the text – I don’t know if that could be an issue for you.
Hope that helps.

**Later edit:
My statement that the former method wouldn’t work for multiple apostrophes was based on something I read – having experimented this morning, it actually seems to work OK for more than one apostrophe. – Rory 11/2/2002**

Reply | Quote

For a problem that must happen to every ADO developer on Earth, it is surprisingly difficult to find this trick!

MS has a VB code sample for a class/ActiveX DLL that doubles the apostrophe: A Class to Prepare Strings for Submission to a Database

Reply | Quote

> every ADO developer on Earth,

And then some ….

SELECT * from [Corporate] WHERE [business]='Hudson's Bay Company'

I just tried the doubling-trick, but it didn’t work for me; the line below returns an empty record set (Run-time error ‘3021’ No Current record).

SELECT * from [Corporate] WHERE [business]='Hudson''s Bay Company'

I am familiar with enclosing in double-quotes if the search-string contains a single quote; and, of course, there’s the Australian method of enclosing in single-quotes if the search-string contains a double quote.

However What does one do in general with a field that could contain several delimiters? What about :

 Mary's "Fairy" Flowers 

as a company name? Neither the double-embrace or the single-embrace will satisfy me.

Does anyone have a general solution that will permit development of a search string for any characters?

If Access97 ( to use but one example) will allow me to enter any character (not just any keyboard character, but ANY ASCII code 000 through 255) in a field, how could one possibly issue a search string for that record if doubling a delimiter won’t work?

Or am I more lost in this than I think I am?

Reply | Quote

Apologies. My doubled-up-quote search wasn’t working because I didn’t code my utility function correctly. My initial essay was appending two extra (!!) single-quotes to the end of the fabricated string, and that is why no records were found.

FWIW I read Gupta’s SQL Base “SQL reference”, and right there on page 2-17 it says:

A string constant must be enclosed in single quotes when used in a SQL command. To include a single quote in a string constant, use two adjacent single quotes.

Now Access/VBA/SQL ain’t Gupta, but that definition fits in pretty well with my understanding of the world.

That’s what made me go back and wonder whether maybe, just maybe, I’d screwed up (again) in my programming.

I had.

Good:

' replace each occurrence of the delimiter by TWO instances of it
    Dim strResult As String ' transient result field
    strResult = strResult & u.strSplitStringAt(strText, strdelim, True)
    strText = u.strSplitStringAt(strText, strdelim, False)
    While strText  ""
        strResult = strResult & strdelim & strdelim
        strResult = strResult & u.strSplitStringAt(strText, strdelim, True)
        strText = u.strSplitStringAt(strText, strdelim, False)
    Wend

    strDouble = strResult

Bad:

' replace each occurrence of the delimiter by TWO instances of it
    Dim strResult As String ' transient result field
    strResult = strResult & u.strSplitStringAt(strText, strdelim, True)
    strText = u.strSplitStringAt(strText, strdelim, False)
    While strText  ""
        strResult = strResult & strdelim & strdelim
        strResult = strResult & u.strSplitStringAt(strText, strdelim, True)
        strText = u.strSplitStringAt(strText, strdelim, False)
    Wend

Reply | Quote

So very close, Rory. This time the program didn’t error out, however, it recorded the entry as #Non Hodgkin’s Lymphoma (NHL)#

Reply | Quote

Mike,
What exactly do you mean by “recorded it”? Do you mean it entered a new record with that value? If so, can you post the code you’re using now? (The # signs were only for use in building the filter string)

Reply | Quote

Very strange : when you use Find or Filter the string with apostrophe is not found.
If you loop through the recordset and compare the string (containing apostrophe) to the field, it is well found.

Reply | Quote

Looping through the recordset is an option, not preferable, but better than making another trip to the database.

Reply | Quote