single quote in search string 2

[diwin]

I have read all the posts showing the following...

Replace(strText,"'", "''"

It isn't helping me.

Now I get...

SELECT * FROM qryDonationSearchOutput WHERE dtmemorialFor LIKE 'D''Andrea*' ORDER BY donationID DESC

Of course I get no results, because that search string can't find D'Andrea.

How do I get my search string to work?

The following is the 'Naked Code'

        If Me.txtMemoriam <> "" Then
        SQLtxtMemoriam = SQLtxtMemoriam & "("
            For counter = 0 To (upperLim - 1)
                MsgBox txtArray(counter)
                If txtArray(counter) <> "" Then
                    SQLtxtMemoriam = SQLtxtMemoriam & "(dtmemorialFor LIKE '" & txtArray(counter)
                End If
                If Right(Me!txtMemoriam, 1) = "*" Then
                    SQLtxtMemoriam = SQLtxtMemoriam & "') OR "
                Else
                    SQLtxtMemoriam = SQLtxtMemoriam & "*" & "') OR "
                End If
            Next
            SQLtxtMemoriam = SQLtxtMemoriam & "(dtmemorialFor LIKE '" & txtArray(counter)
            If Right(Me!txtMemoriam, 1) = "*" Then
                SQLtxtMemoriam = SQLtxtMemoriam & "')"
            Else
                SQLtxtMemoriam = SQLtxtMemoriam & "*" & "')"
            End If
            SQLtxtMemoriam = SQLtxtMemoriam & ")"
        End If
        sSQLWhere = sSQLWhere & "donationID IN (SELECT dtmemorial_FdonationID FROM tblDtMemorial WHERE " & SQLtxtMemoriam & ")"
        
'        MsgBox SQLtxtMemoriam & vbCrLf & vbCrLf & _
''        sSQLWhere
'        Exit Sub
    End If

Daniel Dillon

 

[diwin]

Sorry, I didn't quite grab all the code.
Below is the Naked code....

If Not IsNull(Me.txtMemoriam) Then
        If sSQLWhere <> "" Then
            sSQLWhere = sSQLWhere & " AND "
        End If
        txtArray = Split(Replace(Trim(Me.txtMemoriam), "'", "''"), " ")
        upperLim = UBound(txtArray)
        
        If Me.txtMemoriam <> "" Then
        SQLtxtMemoriam = SQLtxtMemoriam & "("
            For counter = 0 To (upperLim - 1)
                MsgBox txtArray(counter)
                If txtArray(counter) <> "" Then
                    SQLtxtMemoriam = SQLtxtMemoriam & "(dtmemorialFor LIKE '" & txtArray(counter)
                End If
                If Right(Me!txtMemoriam, 1) = "*" Then
                    SQLtxtMemoriam = SQLtxtMemoriam & "') OR "
                Else
                    SQLtxtMemoriam = SQLtxtMemoriam & "*" & "') OR "
                End If
            Next
            SQLtxtMemoriam = SQLtxtMemoriam & "(dtmemorialFor LIKE '" & txtArray(counter)
            If Right(Me!txtMemoriam, 1) = "*" Then
                SQLtxtMemoriam = SQLtxtMemoriam & "')"
            Else
                SQLtxtMemoriam = SQLtxtMemoriam & "*" & "')"
            End If
            SQLtxtMemoriam = SQLtxtMemoriam & ")"
        End If
        sSQLWhere = sSQLWhere & "donationID IN (SELECT dtmemorial_FdonationID FROM tblDtMemorial WHERE " & SQLtxtMemoriam & ")"
        
'        MsgBox SQLtxtMemoriam & vbCrLf & vbCrLf & _
''        sSQLWhere
'        Exit Sub
    End If

Daniel Dillon

 

[abaldwin]

Here is a nifty little function that you may be able to incorporate. Notice that it puts the single quotes into the part of the where string. I have included a snippet on how I used it to test. BTW I found this function of FreeVbCode.com a while back

Public Function prepStringForSQL(ByVal sValue As String) As String

Dim sAns As String
sAns = Replace(sValue, Chr(39), "''")
sAns = "'" & sAns & "'"
prepStringForSQL = sAns


End Function


Private Sub Command1_Click()
'I assume you have an active ado connection object called condata

Dim rsdata As New ADODB.Recordset
dim mstsql as string

mstsql = "SELECT Cus_name FROM table1 WHERE cus_name = " & prepStringForSQL(Text1.Text) & ";"

rsdata.Open mstsql, condata, adOpenDynamic, adLockOptimistic
With rsdata
If .EOF And .BOF Then
MsgBox "Nothing"
Else
Do While Not .EOF
MsgBox !cus_name


.MoveNext
Loop
End If
End With
Set rsdata = Nothing
Set condata = Nothing



End Sub

One of my entries in the cus_name field is D'Andrea. You may have to tweak to get the LIKE to work.



Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[RoyVidar]

Now I get...

SELECT * FROM qryDonationSearchOutput WHERE dtmemorialFor LIKE 'D''Andrea*' ORDER BY donationID DESC

Of course I get no results, because that search string can't find D'Andrea." - you sure? Do you really have D'Andrea in the field you're searching?

Which library are you using? DAO or ADO? If ADO, remember the wildchard is % not *.

In either case, start incremental - only one criterion, make it work, then expand step by step ...

Roy-Vidar

 

[diwin]

It is DAO.
D'Andrea is in the table, as are a few names with O'.
Searching for one Criteria craps out.

Why does it appear in the search string with 2 quotes? is that correct/ I mean, should it have to quotes when I debug.print it???

Is there a particular "'" I should use?

Daniel Dillon

 

[RoyVidar]

Both libraries bomb at a string like this 'te'st' cause the single quote is also the text delimiter used to make Jet understand it's a text criterion, and with just one within the string, it is interpreted as the start or end of a string litteral. Doubling the offending character 'te''st' makes Jet understand/accept it as if it were one single quote. So yes, debug print should show two single quotes in stead of one. This is also what needs to be passed to Jet.

Using DAO, you can also use the "doublequote" technique, i e in stead of doubling up characters within the string, enclose it in quotes - couple of versions

[tt]...where mytext = """ & me!txtControl.value & """"
...where mytext = " & chr$(34) & me!txtControl.value & chr$(34)[/tt]

which both makes the resuliting string look like this

...where mytext = "te'st"

Try starting out simple, only one criterion, if that "craps out", then there's no use adding more, they won't work either ... get one to work, then build it ... If you're using single quote, you'll need to use single quote (chr$(39)) - you're sure it's those thingies and not "`´"?

Roy-Vidar

 

[PCLewis]

The simple answer is that your SQL statement is perfectly fine for DAO:

SELECT * FROM qryDonationSearchOutput WHERE dtmemorialFor LIKE 'D''Andrea*' ORDER BY donationID DESC

You have replaced the single quote with 2 single quotes in the search string whihc is correct. You have included a wildcard '*' which for DAO is correct.

If that query returned no rows then why not believe it? If you use that very query in the query builder, does if give you a result or no data? If the answer is that it gives you a result then look again at the code you are using to open the recordset - are you sure it's DAO and not ADO?

If the query builder gives no results as well then run this in the query builder:

NB: You can put any valid table name in place of MSysObjects in this query. I just chose it because all access databases have it.

SELECT *
FROM (SELECT "D'Andrea" as dtmemorialFor,1 as donationID FROM MSysObjects) as Q1 
WHERE dtmemorialFor LIKE 'D''Andrea*' 
ORDER BY donationID DESC

If this does NOT produce rows and rows with D'Andrea in the first column then you have indeed got a curly problem and should post back the results!!

 

[diwin]

Thanks PCLewis.
You got me to the next step.

As it turns out, a red herring has spoilt the worx. The first time I tried to search for a name with a single quote, D'Andrea, is also the first time I tried to search for a surname in a 2-name field, (Anna D'Andrea). Only the first name can be found (I don't know why!?!?!?) but I can find D'Andrea just fine when I rearrange the name stored in the table with D'Andrea first and Anna as the surname.

So my dilemma still exists, although not as I had first perceived it. How can I get my search string to find any word in the field? A reminder...I really don't understand LIKE fully (...or much fully) Is my SQL statement ok? This is how it looks from debug.print...

SELECT * FROM qryDonationSearchOutput WHERE donationID IN (SELECT dtDesignation_FdonationID FROM tblDTDesignation WHERE dtDesignation_FdesignationTypeID = 2) AND donationID IN (SELECT dtmemorial_FdonationID FROM tblDtMemorial WHERE ((dtmemorialFor LIKE 'Anna*'))) ORDER BY donationID DESC


Daniel Dillon

 

[MajP]

Access 2002 Desktop Developer's Handbook (Litwin, Getz, Underloy p255-257) has an interesting discussion and function to handle embedded quotes. Here are some things to keep in mind:

1) DAO allows double quotes where ADO does not. So this works in DAO but not ADO:

strCriteria = "[CompanyName] = """ & strName & """"

This works in ADO
strCriteria = "[CompanyName] = '" & strName & "'"

2) Their solution for one embedded apostrophe, is to run it through a function, adhHandleQuotes, that doubles the embedded apostrophe. (I do not have the function here but the other replies explain most of what to do)

strCriteria = "[CompanyName] =" & adhHandQuotes(strName)

3) Doubling the embedded apostrophe does not work if you have two or more embedded apostrophes. (ex strName = Joe's and Ed's Grocery). The undocumented solution is to use # to deliminate:

strCriteria = "[CompanyName] = # " & strName & "#")

4) And there is no solution for something like this:
("Joe's #9 Winner's Grill") with both embedded and # and '

 

[RoyVidar]

[tt]...like '*D''Andrea*'...[/tt] - you wan't wildchards on both sides ...

Roy-Vidar

 

[diwin]

Yikes!

Daniel Dillon

 

[RoyVidar]

MajP,
Chapter 6 of the 2k edition is available here Chapter 6: ActiveX Data Objects - but I think usage of hash (#) relates to usage of the .find method and .filter property, not dynamic sql with ADO in general.

Though I'm not sure I agree there's no solution to #4 - it's just not easy with dynamic sql. Try using the parameters collection of the command object. Sample can be found just above the .find/.filter thingies in the linked article, or perhaps here (for instance thread705-1116203, chiph's faq faq709-1526 ...)

Roy-Vidar

 

[diwin]

Oops Roy-Vidar.
Wrong thread.

Daniel Dillon