recordset.find criteria problem

  • Thread starter Thread starter Suzette
  • Start date Start date
S

Suzette

Okay, so I have a problem I'm stumped on. I have to lookup information in a
table. Normally I use the

strCriteria = "FIELD = '" & info & "'"

Found out it won't work with an apostrophe.
Cool, changed it to

strCriteria = "FIELD = " & chr(34) & info & CHR(34)

Way cool. Apostrophe problem solved.

Great, now it gets stuck on a comma. Discovered I can do an if then to do
the second statement if there is an apostrophe and the first if there isn't.

NOW I have a record with an apostrophe AND a comma. What do I do now?

Thanks

Suzette
 
I've never heard of any issues with commas.

Please show us the code you're using that causes the problem.
 
Suzette,
Special characters are a pain. When you need to pick
them out of text (or add them to text, for that matter;
e.g. "Smith&Jones"), you often need to type them double
("Smith&&Jones"). Try doubling your keystroke when you
refer to these characters in a text string. Referring to
a single comma may need to look like:
",,"

Hope this works for you.
 
What you're describing for & is true if you're talking about labels on
forms, but not for data in SQL.
 
Here you go.
strName = rsAddresses.Fields("Name")
If InStr(1, strName, "'") > 0 Then
strFind = "Name = " & Chr(34) & strName & Chr(34)
Else
strFind = "Name = '" & strName & "'"
End If
rsAdds.MoveFirst
rsAdds.Find strFind


The field Name is Timothy D. O'Conner, M.D.

My code only messes up on the apostrophe and comma. Since posting I
discovered that I don't get an error when doing the above but it won't find
it either.

IE:
If InStr(1, strInfo, "'") > 0 Then
strFind = "ID = " & Chr(34) & strInfo & Chr(34)
Else
strFind = "ID = '" & strInfo & "'"
End If
rsAdds.Find strFind
If Not rsAdds.EOF Then ' This ID exists

Where strinfo = "o'connert" will always hit EOF. It won't find the record
even though it's there.

Thanks

Suzette
 
Thanks, Doug. I fumbled this one!
-----Original Message-----
What you're describing for & is true if you're talking about labels on
forms, but not for data in SQL.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)





.
Click to expand...
 
I don't see any obvious reason why that shouldn't work. Are you certain
there aren't other characters in the name (such as extra spaces) that might
be causing it not to be matched?

By the way, unless Name might contain double quotes in it, there's no reason
not to use

strFind = "Name = " & Chr(34) & strName & Chr(34)

for all cases.

Another alternative (assuming you're using Access 2000 or newer) would be to
use

strFind = "Name = '" & Replace(strName, "'", "''"') & "'"

in all cases, where that's

Replace(strName, " ' ", " ' ' ")

I'll also comment that Name may not be a particular good name for a table
field, as Access might get confused with it as a reserved word. If you can't
change the field name, at least put square brackets around it:

strFind = "[Name] = " & Chr(34) & strName & Chr(34)

or

strFind = "[Name] = '" & Replace(strName, "'", "''"') & "'"
 
Chr(34) is a double quote. Your code will fail
if strName includes a single and a double quote.

Instead, you may wish to use:
... ='" & replace(strName,"'","''") & "'"


(david)
 
Back
Top