Microsoft Access recordset FindFirst Question

[Guest]

I need to be able to find string data in a recordset that has some special
characters such as an apostrophe or "&" in the data. I know, one should not
allow that data in the db in the first place but, it is there and the client
wants to find it. Thank you.

Ed Cohen | (e-mail address removed)

 

[John W. Vinson]

I need to be able to find string data in a recordset that has some special
characters such as an apostrophe or "&" in the data. I know, one should not
allow that data in the db in the first place but, it is there and the client
wants to find it. Thank you.

Ed Cohen | (e-mail address removed)

rs.FindFirst "[fieldname] LIKE ""*[&']*"""

should work. The square brackets tell Access to take the enclosed characters
literally; the above will find records where fieldname contains either an
ampersand or an apostrophe.

John W. Vinson [MVP]

 

[Guest]

I need to be able to find string data in a recordset that has some special
characters such as an apostrophe or "&" in the data. I know, one should not
allow that data in the db in the first place but, it is there and the client
wants to find it. Thank you.

Ed Cohen | (e-mail address removed)

rs.FindFirst "[fieldname] LIKE ""*[&']*"""

should work. The square brackets tell Access to take the enclosed characters
literally; the above will find records where fieldname contains either an
ampersand or an apostrophe.

John W. Vinson [MVP]

John,
Thank you for your answer, but I guess I need to give you more
information. Here is the code I have so far:

ElseIf (IsNull(varLastName) = False) And (Len(varLastName) > 0) Then
rst.FindFirst ("LName LIKE '" & varLastName & "'")
If rst.NoMatch Then
bFoundRecord = False
Err.Raise 32000, "FindMemberRecord", "Last Name: " & varLastName
& " Not Found"
Else
Me.Bookmark = rst.Bookmark
bFoundRecord = True
End If
End If

As you can see, I am accepting input from a form that gets the text from the
user. Now, how does one combine what the user typed in, say O'KEEFE with what
you suggested. Thank you.

Ed c.

 

[Douglas J. Steele]

Thank you for your answer, but I guess I need to give you more
information. Here is the code I have so far:

ElseIf (IsNull(varLastName) = False) And (Len(varLastName) > 0) Then
rst.FindFirst ("LName LIKE '" & varLastName & "'")
If rst.NoMatch Then
bFoundRecord = False
Err.Raise 32000, "FindMemberRecord", "Last Name: " &
varLastName
& " Not Found"
Else
Me.Bookmark = rst.Bookmark
bFoundRecord = True
End If
End If

As you can see, I am accepting input from a form that gets the text from
the
user. Now, how does one combine what the user typed in, say O'KEEFE with
what
you suggested. Thank you.

rst.FindFirst ("LName LIKE '" & Replace(varLastName, "'", "''") & "'")

Exagerated for clarity, that's

rst.FindFirst ("LName LIKE '" & Replace(varLastName, " ' ", " ' ' ") & "'")

I'm assuming that varLastName is going to include the wildcard character(s),
since there's no point using LIKE unless it does.

 

[Guest]

rst.FindFirst ("LName LIKE '" & Replace(varLastName, "'", "''") & "'")

Exagerated for clarity, that's

rst.FindFirst ("LName LIKE '" & Replace(varLastName, " ' ", " ' ' ") & "'")

I'm assuming that varLastName is going to include the wildcard character(s),
since there's no point using LIKE unless it does.

Thank you!

 

[John W. Vinson]

As you can see, I am accepting input from a form that gets the text from the
user. Now, how does one combine what the user typed in, say O'KEEFE with what
you suggested. Thank you.

Ed c.

Sorry - I did misunderstand the question! I thought you were trying to find
the apostrophe.

Just use doublequotes to delimit the string, instead of singlequotes. To
include a doublequote in a doublequote delimited string use a double
doublequote (how's THAT for doubletalk):

rst.FindFirst ("LName LIKE """ & varLastName & """")

That's three " characters after the LIKE, four at the end of the string. The
two consecutive doublequotes are translated into one - e.g.

Dim strSQL As String
strSQL = "LName LIKE """ & varLastName & """"

will set strSQL to

LName LIKE "O'KEEFE"

which will work correctly.

John W. Vinson [MVP]

 

[David W. Fenton]

Just use doublequotes to delimit the string, instead of
singlequotes. To include a doublequote in a doublequote delimited
string use a double doublequote (how's THAT for doubletalk):

rst.FindFirst ("LName LIKE """ & varLastName & """")

That's three " characters after the LIKE, four at the end of the
string. The two consecutive doublequotes are translated into one -
e.g.

Dim strSQL As String
strSQL = "LName LIKE """ & varLastName & """"

will set strSQL to

LName LIKE "O'KEEFE"

which will work correctly.

Another, much easier way to do this is to declare a constant that
stores the double quote value and then use that:

In a global module's declaration section:

Const strQuote = """"

Used in your code:

Dim strSQL As String

strSQL = "LName LIKE " & strQuote & varLastName & strQuote

I find that much easier to deal with. I also tend to use STR_QUOTE
as my constant name, because then I can type STR_ and then
Ctrl-Space and Intellisense will fill out the rest of the variable
name for me.

 

[John W. Vinson]

Another, much easier way to do this is to declare a constant that
stores the double quote value and then use that:

In a global module's declaration section:

Const strQuote = """"

Used in your code:

Dim strSQL As String

strSQL = "LName LIKE " & strQuote & varLastName & strQuote

That's certainly more readable.

John W. Vinson [MVP]

 

[Douglas J. Steele]

That's certainly more readable.

As is

strSQL = "LName LIKE " & Chr$(34) & varLastName & Chr$(34)

 

[David W. Fenton]

As is

strSQL = "LName LIKE " & Chr$(34) & varLastName & Chr$(34)

Yes, but the latter requires multiple calls to a function at
runtime, whereas the constant is compiled (hardw-red) into the code,
with no function call at runtime.

Any time you're calling a single function multiple times that
returns the exact same data in the same code context, it makes more
sense to assign the output of the function to a variable so that you
call the function only once.

The exception to this would be in contexts where you have no
variable declaration, such as in a query expression. Then, you get
the benefit of the query optmizer, which will be smart enough to see
that Chr$(34) needs to be called only once.