FindFirst and double quotes

[jdraw]

You should always include
Option Explicit as second line in each module

 

[Josef P.]

I don't get any errors just no match.

Can you provide a sample file where the problem can be seen?

 

[jdraw]

For what it's worth:

Using the same table and records as posted in #12, this works when

1. Set rs = db.OpenRecordset("select * from acanvend", dbReadOnly) or
2. Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot, dbReadOnly)
3. Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot)

It results in NoMatch (doesn't work) when using dbOpenDynaset
Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset) OR
Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset, dbReadOnly)

Sub DoubleQuoteAgain()
    Dim rs As DAO.Recordset
    Dim findcriteria As String
    Dim db As DAO.Database
    Set db = CurrentDb
    'Set rs = db.OpenRecordset("select * from acanvend", dbReadOnly)                             'works
    'Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot)                     'works
    'Set rs = db.OpenRecordset("select * from acanvend", dbOpenSnapshot, dbReadOnly)'works
    'Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset)                        'does not work
    Set rs = db.OpenRecordset("select * from acanvend", dbOpenDynaset, dbReadOnly)    'does not work
    findcriteria = "acanvend.terr_cd = '""D''Loro""'"  ': from Cheekybuddha
    Debug.Print findcriteria
    rs.FindFirst findcriteria
    If rs.NoMatch Then
        Debug.Print "No matching record was found"
    End If
    Debug.Print rs!vend_num; rs!TERR_CD
    rs.Close
    If Not rs Is Nothing Then Set rs = Nothing
End Sub

 

[cheekybuddha]

@jdraw,

Since you're testing, what happens if you try:

Sub DoubleQuoteOnceMore()
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim db As DAO.Database
    Set db = CurrentDb
    sql = "SELECT * FROM acanvend WHERE acanvend.terr_cd = '""D''Loro""'"
    Debug.Print sql
    'Set rs = db.OpenRecordset(sql , dbReadOnly)
    'Set rs = db.OpenRecordset(sql, dbOpenSnapshot) ' identical to above
    'Set rs = db.OpenRecordset(sql, dbOpenSnapshot, dbReadOnly)
    'Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
    If Not rs.EOF Then Debug.Print rs!vend_num; rs!TERR_CD
    rs.Close
    If Not rs Is Nothing Then Set rs = Nothing
End Sub

 

[jdraw]

Hi David,

This resulted in
SELECT * FROM acanvend WHERE acanvend.terr_cd = '"D''Loro"'
4 "D'Loro"
with all 5 versions of the Set rs....

?? Is it possible that FindFirst is not compatible with dbOpenDynaSet??

According to M$oft documentation
FindFirst
Locates the first record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).

 

[cheekybuddha]

So each variation produced the desired result with:

Debug.Print rs!vend_num; rs!TERR_CD

?

 

[Josef P.]

Could reproduce it now: It must be an indexed data field, so that it is not found when comparing with =.
With like without * it works.

And it gets even better:
fldSAdderess1 = ''+'"Rye Roxley''s"' => Match found

 

[jdraw]

So each variation produced the desired result with:

Debug.Print rs!vend_num; rs!TERR_CD

?

Yes. I was editing my response to add that info.

As you can see in the graphic below, field Terr_Cd is indexed (duplicates OK)

 

[Josef P.]

For me, this is a bug.

Results with indexed field:
T1 = 'ab''c' => no match
T1 = "ab'c" => match found
T1 = 'ab"c' => match found
T1 = "ab""c" => no match

T1 = '' + 'ab''c' => match found
T1 like 'ab''c' => match found
T1 like 'ab''c*' => no match

Note: records with ab'c and ab"c exists in table.