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?
 
simonbennettConnect With a Mentor Commented:
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
 
mardonAuthor Commented:
Edited text of question.
0
 
simonbennettCommented:
Yup - it's the apoptrophe. Try

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

HTH

Simon
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
mardonAuthor Commented:
Sorry it took so long!!!!
0
 
bruintjeCommented:
thanks for finalizing
0
All Courses

From novice to tech pro — start learning today.