Microsoft Access recordset FindFirst Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)
 
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)
Click to expand...

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 W. Vinson said:
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)
Click to expand...

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]
Click to expand...
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.
 
Ed Cohen said:
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.
Click to expand...


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.
 
Douglas J. Steele said:
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.
Click to expand...
Thank you!
 
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.
Click to expand...

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]
 
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.
Click to expand...

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.
 
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
Click to expand...

That's certainly more readable.

John W. Vinson [MVP]
 
As is

strSQL = "LName LIKE " & Chr$(34) & varLastName & Chr$(34)
Click to expand...

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.
 
Back
Top