Apostrophe Problem

I have a database with customers names such as O'Neil. This presents a problem when I look them up after the initial entry. The look-up uses an auto fill so it reduces typing. The name comes up as it should (O'Neil) but when I execute the query button to take me to the customer page I get the following message: Syntax error (missing operator) in query expression '[ClientCompanyName]='O'Neil"
Now that I now the the problem exists my work around is to simply not use an apostrophe when I enter a new customer. The problem is when I print a letter or anything else obviously their name is wrong There must be a way to enter these names correctly i.e, O'Neil vs O Neil and have the query work as it does with all the other names.
kkeeglerAsked:
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.

jschrismanCommented:
To query data which contains an apostrophe you can put double quotes around it. Access will then pull the data.

Example:
The variable lastName is a string which contains "O'Neil".

To do a dlookup on this name use this-
DLookUp("LName", "NameTbl", "LName = """ & lastName & """");

or if you are building a query in a string name qryString-

qryString = "select LName from NameTbl where LName = """ & lastName & """;"

You may also need to cast lastName to a string if it's a variant data type. Just replace all lastName references with CStr(lastName).
0
brewdogCommented:
You can either use jschrisman's idea (the double-double quotes every time you want a quote) or a chr(34) (ascii character for quotation marks). of course, if anyone's name is "The Man" you'll still have a problem, but that's a bit unlikely . . . :o)
0
MarineCommented:
Here is a small function which you can implement to your code to eliminate the apostraph

Function CheckStr(str)
If str <> "" Then
   If Instr(str,"'")<>0 Then
CheckStr = Replace(str,"'","''")
    Else
    CheckStr = n
End If
End If
End Function

works fine.
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!

MarineCommented:
sorry replace the n with str

Function CheckStr(str)
If str <> "" Then
   If Instr(str,"'")<>0 Then
CheckStr = Replace(str,"'","''")
    Else
    CheckStr = str
End If
End If
End Function

0
RuchiCommented:
This is to fill format the field you want to store in the database if there is some apostrophes in the field.


Public Function Apostrophe(sFieldString As String) As String
If InStr(sFieldString, "'") Then
Dim iLen As Integer
Dim ii As Integer
Dim apostr As Integer
iLen = Len(sFieldString)
ii = 1
Do While ii <= iLen
    If Mid(sFieldString, ii, 1) = "'" Then
    apostr = ii
        sFieldString = Left(sFieldString, apostr) & "'" & Right(sFieldString, iLen - apostr)
        iLen = Len(sFieldString)
        ii = ii + 1
       
    End If
    ii = ii + 1
    Loop
    End If
   
Apostrophe = sFieldString
End Function



For example,
myVar=apostrophe(myvar)
SSQL="INSERT INTO tablename (FirstName) VALUES (" & chr(39) & myvar & chr(39) & ")"
0
RuchiCommented:
I gave my comment a long time ago...
If you do code in visual basic, try this one.

Use the SrchReplace function. It wlil clean up any embedded quotes.

Function SrchReplace(ByVal sStringToFix As String) As String

Dim iPosition As Integer 'where is the offending char?
Dim sCharToReplace As String 'which char do we want to replace?
Dim sReplaceWith As String 'what should it be replaced with?
Dim sTempString As String 'build the correct returned string

sCharToReplace = "'"
sReplaceWith = "''"

iPosition = InStr(sStringToFix, sCharToReplace)

Do While iPosition
sTempString = ""
sTempString = sTempString & Left$(sStringToFix, iPosition - 1)
sTempString = sTempString & sReplaceWith
sTempString = sTempString & _
Mid$(sStringToFix, iPosition + 1, Len(sStringToFix))
sStringToFix = sTempString
iPosition = InStr(iPosition + 2, sStringToFix, sCharToReplace)
Loop

SrchReplace = sTempString

End Function


If use listbox, then use this..

Private Sub List1_DblClick()
'get the item the user clicks on and assign it
If (InStr(List1, "'")) Then
    your code...
ie:
   Text1.Text = SrchReplace(List1)

Else

   your code
End If

Unload frmFind

End Sub

If you use the listbox... be sure to add ""

i.e.

While Not Data1.Recordset.EOF
        List1.AddItem Data1.Recordset.Fields(0) & ""
        Data1.Recordset.MoveNext
Wend




Or, you can read the article...

http://support.microsoft.com/support/KB/ARTICLES/Q113/9/55.asp
0
MarineCommented:
Mine just as good as your guys.
0
hnasrCommented:
kkeegler -

What do you want to do exactly?
If it is only to use ' or " in query, give your feedback to both jschrisman and brewdog.

If it is different then you have to read all comments and try them in your environment.  Your feedback is very important.  You see, because you did not comment yet, many comments came up with different ideas, even with different objectives.

Many things to digest here. :o)
0
kkeeglerAuthor Commented:
With all the varied suggestions I need to be more specific in the description.
Example: Mary A O'Neil or Mary's Flower Shop is the name of my customer.The entry of the data is obviously not a problem. The problem arises the next time they call back and I need to look them up and prepare the forms I use. The look-up is fine using an autofill to save on repetitive typing. Then I actually try to retreive the record from the database I get that syntax error.

I thought the best thing to do was copy the command from the button that returns the record. Maybe this will clarify the approach to take.

Thanks for all the options and hopefuly this will help.

_________________________________________

Option Compare Database
Option Explicit

Private Sub cboMainMenuClientNum_Click()
 Me.Repaint
End Sub

Private Sub cboMainMenuClientNum_GotFocus()
 Me.Repaint
End Sub

Private Sub cmdAddNewSubscriber_Click()
On Error GoTo Err_cmdAddNewSubscriber_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmClientReview"
   
    stLinkCriteria = "[ClientCompanyName]=" & "'" & Me![cboMainMenuClientNum] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    DoCmd.Close , "frmMainMenu", acSaveYes
Exit_cmdAddNewSubscriber_Click:
    Exit Sub

Err_cmdAddNewSubscriber_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNewSubscriber_Click
   
End Sub
Private Sub cmdAddNewSubject_Click()
On Error GoTo Err_cmdAddNewSubject_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmNewSubjectEntry"
   
    stLinkCriteria = "[SubjectSSN]=" & "'" & Me![txtSubjectSSN] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdAddNewSubject_Click:
    Exit Sub

Err_cmdAddNewSubject_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNewSubject_Click
   
End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click


    DoCmd.Quit

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click
   
End Sub
Private Sub cmdFormCustomClientForms_Click()
On Error GoTo Err_cmdFormCustomClientForms_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmCustomClientForms"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    DoCmd.Close acForm, "frmMainMenu"

Exit_cmdFormCustomClientForms_Click:
    Exit Sub

Err_cmdFormCustomClientForms_Click:
    MsgBox Err.Description
    Resume Exit_cmdFormCustomClientForms_Click
   
End Sub
0
brewdogCommented:
It looks like this line is the culprit:

stLinkCriteria = "[ClientCompanyName]=" & "'" & Me![cboMainMenuClientNum] & "'"

in this procedure:

Private Sub cmdAddNewSubscriber_Click()

If you change this to either use the chr(34)s or the "" instead of "'" (double-single-double), you should be fine.
0
jschrismanCommented:
Right -

stLinkCriteria = "[ClientCompanyName]=" & "'" & Me![cboMainMenuClientNum] & "'"

should be changed to -

stLinkCriteria = "[ClientCompanyName]=""" & Me![cboMainMenuClientNum] & """"

or to use Brewdog's method -

stLinkCriteria = "[ClientCompanyName]=" & chr$(34) & Me![cboMainMenuClientNum] & chr$(34)
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
kkeeglerAuthor Commented:
Thanks for the prompt answer. I used the following solution:
stLinkCriteria = "[ClientCompanyName]=""" & Me![cboMainMenuClientNum] & """"

It tested fine.
Like the PGA "You Guys Are Good"
Thanks again
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.