FindFirst and double quotes (1 Viewer)

[Rye Roxley]

Hi
I know escaping double quotes has been covered but I just cannot understand it in my situation.
I wish to search through a record set with rs.FindFirst. The entry I wish to find is already within double quotes e.g.
"Street Name"
So I set a variable
Address1 = """Street Name"""
escapedAddress1 = Replace(Address1, """", """""")
findCriteria = "fldSAdderess1=""" & escapedAddress1 & """"
rsAddresses.FindFirst findCriteria

Does not find a match.
What am I doing wrong?

 

[theDBguy]

Hi. Welcome to AWF!

Are you saying you're data contains double quotes in your table?

 

[June7]

Try apostrophe delimiters. Where is the parameter coming from - a textbox?

findCriteria = "fldSAdderess1='" & Me.tbxAddress & "'"

But then might be an issue with apostrophes in data.

Can use Chr(34) instead of typing actual quote character.

Is field name "fldAdderess" really spelled with extra "e"?

 

[jdraw]

I mocked up a routine using this table, where Terr_cd = "Toronto" for vend_num 4.
This routine successfully finds "Toronto".

Sub doublequote()
'SELECT Acanvend.TERR_CD
'FROM Acanvend
'WHERE Acanvend.TERR_CD like """Toronto""";  'This SQL works with the quotes as shown.

    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)
    findcriteria = "acanvend.terr_cd  =  ""Toronto"""          'can be = or Like
    rs.FindFirst findcriteria

End Sub

 

[Josef P.]

address1 = "Street ""abc"" 123"
Debug.Print address1  ' => Street "abc" 123
escapedAddress1 = Replace(Address1, """", """""") ' <-- Important line to avoid SQL injection
findCriteria = "fldSAdderess1=""" & escapedAddress1 & """"
debug.Print findCriteria

I personally prefer the ' as text delimiter in SQL:

address1 = "Street ""abc"" 12'3"
Debug.Print address1  ' => Street "abc" 12'3
escapedAddress1 = Replace(Address1, "'", "''") ' <-- Important line to avoid SQL injection
findCriteria = "fldSAdderess1='" & escapedAddress1 & "'"
debug.Print findCriteria

 

[Rye Roxley]

Many Thanks all I will try your suggestions later.

Are you saying you're data contains double quotes in your table?

Yep, some of the data has double quotes and there is nothing I can do about it! It is how it is imported and then how it needs to be exported later!

Is field name "fldAdderess" really spelled with extra "e"?

No it's a typo!

 

[theDBguy]

Many Thanks all I will try your suggestions later.

Yep, some of the data has double quotes and there is nothing I can do about it! It is how it is imported and then how it needs to be exported later!

In that case, I'm with @June7. I would also use a single quote as the delimiter and use Replace() to escape any single quotes in the data.

 

[Pat Hartman]

Who can decode the multiple """""""'s I declare a constant to make these strings easier to read. Put it in a standard module, NOT in the class module of a form or report.

Public Const QUOTE = """"

Address1 = QUOTE & "Street Name" & QUOTE

Using a single quote with names won't work when the string contains a quote such as O'Brien. Someone posted code that gets around this. I think it was MagP

 

[ebs17]

It is how it is imported and then how it needs to be exported later!

That's exactly how it is.
As part of an import specification for text import, you can enter text separators and omit them when importing, and add them again when exporting.
As you can see, carrying such superfluous things in table data causes many problems.

 

[Rye Roxley]

Using a single quote with names won't work when the string contains a quote such as O'Brien. Someone posted code that gets around this. I think it was MagP

Can someone help me find this, please?
Currently, I am using this but there must be a more elegant way:

Sub Test()

    Dim rsSites As DAO.Recordset
    Dim Quote As String
    Dim findcriteria As String
    Set db = CurrentDb
    Set rsSites = db.OpenRecordset("tblSites", dbOpenDynaset)
    
    'Quote = "Rye Roxley"
    Quote = "Rye Roxley's"
    'Quote = """Rye Roxley"""
    Debug.Print Quote
    If InStr(Quote, "'") > 0 Then
        escapedSiteID = Replace(Quote, """", """""")
        findcriteria = "fldSiteName=""" & escapedSiteID & """"
        Debug.Print findcriteria
        rsSites.FindFirst findcriteria
        Debug.Print rsSites.AbsolutePosition
    Else
        escapedSiteID = Replace(Quote, "'", "''")
        findcriteria = "fldSiteName='" & escapedSiteID & "'"
        Debug.Print findcriteria
        rsSites.FindFirst findcriteria
        Debug.Print rsSites.AbsolutePosition
    End If
    If rsSites.NoMatch Then
        Debug.Print "Not Found"
        Else
        Debug.Print "Found"
    End If

End Sub

 

[Gasman]

The member would be @MajP
Search the site with his name in the Member field.

 

[jdraw]

Adjusted earlier mockup. And this SQL works in my testing.
SELECT Acanvend.TERR_CD
FROM Acanvend
WHERE Acanvend.TERR_CD like """D'Loro""";



The table
Acanvend Acanvend

VEND_NUM	CITY_NM	TERR_CD	PROV_CD	CNTRY_CD	EFF_DT	myByte
1​	0003012370	Shelburne	ON	CA	11-Oct-16​	2​
2​	0003012370	Toronto	ON	CA	03-Jul-13​	22​
3​	0003012370	Shelburne	ON	CA	17-Jun-16​	3​
4​	0003012370	"D'Loro"	ON	CA	31-May-13​	4​
5​	0003012370	Melancthon	ON	CA	17-Apr-18​	5​
6​	0003012370	Melancthon	ON	CA	11-Jun-18​	6​
7​	0003012373	GHlancthon	ON	CA	11-Jun-16​	32​
8​	0003012373	GHlancth	NB	CA	06-Nov-18​	16​
9​	0003012373	GHlancthon	ON	CA	11-Sep-18​	42​

Revised procedure to deal with embedded single quote. Working

' ----------------------------------------------------------------
' Procedure Name: doublequote
' Purpose: Demo in support of OP showing findfirst where target is enclosed in double quotes
'  and target contains a single quote  (D'Loro)
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 23-Aug-23
'See https://www.access-programmers.co.uk/forums/threads/findfirst-and-double-quotes.328577/#post-1887429
' ----------------------------------------------------------------
Sub doublequote()
'SELECT Acanvend.TERR_CD
'FROM Acanvend
'WHERE Acanvend.TERR_CD like """D'Loro""";

   
 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)
    findcriteria = "acanvend.terr_cd like  '*D''Loro*'"
     Debug.Print findcriteria
    rs.FindFirst findcriteria
        Debug.Print rs!vend_num; rs!TERR_CD
End Sub

This code works
acanvend.terr_cd like '*D''Loro*'
4 "D'Loro"

I did not get it to work with an equal condition

I tried various iterations with double quote and the routine always reported record with vend_num 1??
acanvend.terr_cd = "D''Loro"
1 Shelburne
acanvend.terr_cd = "D'Loro"
1 Shelburne
acanvend.terr_cd = 'D''Loro'
1 Shelburne

 

[cheekybuddha]

I did not get it to work with an equal condition

Did you try:

findcriteria = "acanvend.terr_cd =  '""D''Loro""'"
Debug.Print findcriteria

 

[jdraw]

Thanks David.
That was at least one of the variations I had not tried.

 

[Rye Roxley]

Thanks for the help and suggestions I have made some progress!
This works:

Sub doublequote()
    Dim rsSites As DAO.Recordset
    Dim findcriteria As String
    Dim db As DAO.Database
    Set db = CurrentDb
    Set rsSites = db.OpenRecordset("tblSites", dbReadOnly)
    
    'Quote = "Rye Roxley" 'Found
    'Quote = "Rye Roxley's"
    'Quote = """Rye Roxley"""
    Quote = """Rye Roxley's"""
    Quote = Replace(Quote, "'", "''")
    Quote = Chr(39) & Quote & Chr(39)
    findcriteria = "fldSiteName  =  " & Quote        
    Debug.Print findcriteria
    rsSites.FindFirst findcriteria
    Debug.Print rsSites!fldEstablishmentI

End Sub

But if I change it to:

Set rsSites = db.OpenRecordset("tblSites", dbOpenDynaset)

It fails to find the records.
Why is this?
TIA

 

[cheekybuddha]

So, what output did the Debug.Print give? Can you post it here?

It looks like you've added the double quotes twice:
First here:
Quote = """Rye Roxley's"""
Then again here:
Quote = Chr(39) & Quote & Chr(39)

 

[Rye Roxley]

So, what output did the Debug.Print give? Can you post it here?

fldSiteName = '"Rye Roxley''s"'

This matched with the record that contains "Rye Roxley's" (including the opening and closing double quote).
Only if I use

    Set rsSites = db.OpenRecordset("tblSites", dbReadOnly)

I get no match if I use:

Set rsSites = db.OpenRecordset("tblSites", dbOpenDynaset)

 

[Josef P.]

dbReadOnly is wrong at this point. Actually it says dbOpenSnapshot (has the same value as dbReadOnly).
For more info see: RecordsetTypeEnum enumeration (DAO) | Microsoft Learn

I get no match if I use:

Do you maybe get an error message instead because you use e.g. MSSQL-Server as backend and an identity data field?

 

[cheekybuddha]

fldSiteName = '"Rye Roxley''s"'

Oh yes - confused between Chr(34) and Chr(39) - doh!

Also, do you have Option Explicit declared at the top of every code module (above or below Option Compare Database)?

Asking, because of Josef P.'s point.

 

[Rye Roxley]

Also, do you have Option Explicit declared at the top of every code module (above or below Option Compare Database)?

No Just

Option Compare Database

I don't get any errors just no match.