FindFirst and double quotes

Rye Roxley

New member
Local time
Today, 01:12
Joined
Aug 22, 2023
Messages
6
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?
 
Hi. Welcome to AWF!

Are you saying you're data contains double quotes in your table?
 
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"?
 
Last edited:
I mocked up a routine using this table, where Terr_cd = "Toronto" for vend_num 4.
This routine successfully finds "Toronto".

Code:
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
 
Code:
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:
Code:
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
 
Last edited:
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!
 
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.
 
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
 
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.
 
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:
Code:
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
 
The member would be @MajP
Search the site with his name in the Member field.
 
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_NUMCITY_NMTERR_CDPROV_CDCNTRY_CDEFF_DTmyByte
1​
0003012370ShelburneONCA
11-Oct-16​
2​
2​
0003012370TorontoONCA
03-Jul-13​
22​
3​
0003012370ShelburneONCA
17-Jun-16​
3​
4​
0003012370"D'Loro"ONCA
31-May-13​
4​
5​
0003012370MelancthonONCA
17-Apr-18​
5​
6​
0003012370MelancthonONCA
11-Jun-18​
6​
7​
0003012373GHlancthonONCA
11-Jun-16​
32​
8​
0003012373GHlancthNBCA
06-Nov-18​
16​
9​
0003012373GHlancthonONCA
11-Sep-18​
42​

Revised procedure to deal with embedded single quote. Working

Code:
' ----------------------------------------------------------------
' 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
 
Last edited:
Thanks David.(y)
That was at least one of the variations I had not tried.

2023-08-23 14_11_15-Microsoft Visual Basic for Applications - Database1_o365_OK - [ZZ_ScratchP...png
 
Thanks for the help and suggestions I have made some progress!
This works:
Code:
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:
Code:
Set rsSites = db.OpenRecordset("tblSites", dbOpenDynaset)
It fails to find the records.
Why is this?
TIA
 
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)
 
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
Code:
    Set rsSites = db.OpenRecordset("tblSites", dbReadOnly)
I get no match if I use:
Code:
Set rsSites = db.OpenRecordset("tblSites", dbOpenDynaset)
 
fldSiteName = '"Rye Roxley''s"'
Oh yes - confused between Chr(34) and Chr(39) - doh! o_O

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.
 

Users who are viewing this thread

Back
Top Bottom