.FindFirst

[kirkm]

If the criteria for FindFirst contains an apostrophe what is the correct syntax?
I have a workaround using Replace but it's messy and I suspect a 'proper' method exists.

 

[CJ_London]

use replace to replace a single with two singles

replace(myvalue,"'","''")

 

[kirkm]

Ah, ok. But what about the single quote in e.g. a word like "don't" ?

 

[CJ_London]

don't understand, that is what you do, Better provide an example or your workaround and some example data/criteria so I can understand what you mean

 

[Grumm]

Does the criteria come from a form input ?
If you use the raw value of the form, the apostrophe should not be an issue.

Direct use of form control's raw value is an extremely useful method. Some of the salient advantages are:
(a) No need to fix any embedded quotes, whether single or double or a combination of both.
(b) No need to worry about specific delimiters while concatenating values for various data types (e.g. ' for text type and # for date type data).
(c) No risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US date format (mm/dd/yyyy).

 

[kirkm]

I'm not following the replies so well. What difference where the string comes from (and what is "raw value")?
From MSDN
'Search for the first matching record
rst.FindFirst "[OrgName] LIKE '*parts*'"

This will fail if "parts" is "part's".
Replacing all single quotes will also fail as "part's" is not "part''s"
I have a solution but what is Excels solution ?

 

[MajP]

are you writing it literally or is it a variable? If writing it out simply use two single quotes

st.FindFirst "[OrgName] LIKE '*part''s*'"

This looks strange, but thats the rule. It will return Part's and would not return Part''s
To return Part''s you would in fact need four single quotes

 

[kirkm]

Really? Ok I aplogise to CJ.
I was using what seemed a hack and didn't really like it.

tmp = "Title = #" & w & "#"
tmp = Replace(tmp, "#", Chr$(34))
.FindFirst tmp

 

[MajP]

w = "Don't"
.findfirst "Title = '" & replace(w,"'","''") & "'"

To be clear in the replace function the second argument using underscores for visibility "_'_" and the replacement is "_'_'_"

The findFirst with underscores is then
.findfirst "Title = '_" & replace(w,"_'_","_'_'_") & "_'_"
The whole thing should then resolve to
Title = 'Don''t'

You may find this helpful. I use it all the time when I have lots of delimeters.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

The above then becomes simply
.findfirst "Title = " & csql(w)