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?
mardonAsked:
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.

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

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

HTH

Simon
0
TrygveCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

brewdogCommented:
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
mardonAuthor 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
TrygveCommented:
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
bruintjeCommented:
Hi Mardon,

Any update on this question?

in an effort to clean up old open questions

your options are:

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
outside this question thread, please add it.
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

QID/link. http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

Please click this Help Desk link for Member Guidelines, Member Agreement and the Question/Answer process:
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
mardonAuthor 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
simonbennettCommented:
Hiya

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

Thanks for your response

Simon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mardonAuthor Commented:
Sorry it took so long!!!!
0
bruintjeCommented:
thanks for finalizing
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.