# FindFirst Criteria

Here is the problem:

strCriteria = "[Name] = '" & cboName & "'"
rst.FindFirst strCriteria

This works fine when cboName = John Brown Garage, when cboName = John Brown's Garage, an error occurs.  I think it is caused by the apostrophe in Brown's.  How do I fix it?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
Edited text of question.
0
Commented:
Yup - it's the apoptrophe. Try

strCriteria = "[Name] = " & chr(34) & cboName & chr(34)

HTH

Simon
0
Commented:
Another solution is this.

Copy/Paste the function below into a module and then use

strCriteria = "[Name] = '" & FindAndReplace(cboName,"'","''") & "'"
rst.FindFirst strCriteria

Replacing the 's with ''s takes care of the problem. The function can turn out quite usefull if you are dealing with exports, build your own SQL statements etc.

''************ Code Start **********
'This code was originally written by Alden Streeter.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Alden Streeter
'
Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String
Dim intPtr As Integer
If Len(strFindString) > 0 Then  'catch if try to find empty string
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
strReplaceString
strInString = Mid(strInString, intPtr + Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
End Function
'************ Code End **********
0
Commented:
ah, the never-ending discussion in Access. :o)

If your entries will ever have double-quotes in them, say Ralph "Joker" Smith, then Trygve's is much preferred. If that's not going to happen, the chr(34)s are my choice, too.
0
Author Commented:
Thanks all -

Since I've already used simonbennet answer and it works great - please post it as an answer so I can give you the points.

Thanks again.
0
Commented:
If you want scaleability, and you should..., then using chr(34) is not the choice. SQL Server, as an example, does not recognize " as string delimiter.

If your project is going to stay in MDBs forever, then use it happily :-)

If so, I would suggest assigning it to a constant that makes the code more readable

Global Const constQuotes = chr(34)

or something like that.
0
Commented:
Hi Mardon,

Any update on this question?

in an effort to clean up old open questions

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the
participants why you wish to do this, and allow for Expert response.  This choice will include a refund
to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
--> Post comments for expert of your intention to delete and why
--> You cannot delete a question with comments, special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below, include the question

Click you Member Profile to view your question history and keep them all current with updates as the
collaboration effort continues. http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

------------>  EXPERTS:    Please leave any comments regarding this question here on closing recommendations
if this item remains inactive another seven (7) days.

:O)Bruintje
0
Author Commented:
If you look at the thread, I ahd asked simonbennet to post his comment (under the old system) as an answer and I would accept it.  I'm still willing to do that.  should I do something else?
0
Commented:
Hiya

You should be able to "accept comment as answer" on the comment. Failing that just post an answer.

Simon
0

Experts Exchange Solution brought to you by