Apostrophe Problem

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).

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)

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.

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

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) & ")"

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

Mine just as good as your guys.

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)

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

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.

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