Results 1 to 7 of 7
  1. #1
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44

    Error 3077 - use of apostrophe

    Good day.

    I have a combo box (txtSearch) but it's not allowing me to search for names which have an apostrophe. I get Error 3077. I looked at several posts on this but none were able to help me.

    Here is my code:

    Private Sub txtSearch_AfterUpdate()
    'Find record based on contents of txtSearch.
    Dim strSearch As String
    On Error GoTo errHandler
    'Delimited for text search.
    strSearch = "Name = " & Chr(39) & Me!txtSearch.Value & Chr(39)

    'Delimited for numeric values.


    'strSearch = "OrderID = " & Me!txtSearch.Value

    'Find the record.
    Me.RecordsetClone.FindFirst strSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Exit Sub
    errHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & _
    Err.Description
    End Sub

    Thanks in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    strSearch = "Name = " & Chr(34) & Me!txtSearch.Value & Chr(34)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    strSearch = "Name = " & Chr(39) & Me!txtSearch.Value & Chr(39)
    Change to:

    Code:
    strSearch = "Name = " & chr(34) & Chr(39) & Me!txtSearch.Value & Chr(39) & chr(34)

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Not surprising. You are using single quotes (= apostrophes = chr(39)) to delimit a string which has an apostrophe in it, which causes an error.

    If you know that the text to search for will never contain double-quotes ("), you can use those as delimiters:

    strSearch = "Name = " & Chr(34) & Me!txtSearch.Value & Chr(34)

    If your search string could contain either single- or double-quotes (but not both), you could do something like this:

    Code:
    Private Sub txtSearch_AfterUpdate()
     'Find record based on contents of txtSearch.
      Dim strSearch As String, Delim as string
      On Error GoTo errHandler
      'Delimited for text search.
      Delim = chr(39) ' start with single quote
      if instr( Me!txtSearch.Value,"'") <> 0 then
        Delim = chr(34)   ' Change delimiter to double-quote
      endif
      strSearch = "Name = " & Delim & Me!txtSearch.Value & Delim
    
      'Delimited for numeric values.
      'strSearch = "OrderID = " & Me!txtSearch.Value
    
      'Find the record.
      Me.RecordsetClone.FindFirst strSearch
      Me.Bookmark = Me.RecordsetClone.Bookmark
        Exit Sub
     errHandler:
      MsgBox "Error No: " & Err.Number & "; Description: " & _
       Err.Description
     End Sub

  5. #5
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Quote Originally Posted by apr pillai View Post
    Change to:

    Code:
    strSearch = "Name = " & chr(34) & Chr(39) & Me!txtSearch.Value & Chr(39) & chr(34)
    This got rid of the error, but it's no longer bringing up the record of who I typed in.

  6. #6
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Thanks John and pbaldy. I just changed the code to chr(34). Works great now.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. .findFirst 3077 syntax error
    By RonL in forum Programming
    Replies: 3
    Last Post: 04-05-2015, 04:22 PM
  2. Issues using apostrophe
    By rosscortb in forum Access
    Replies: 5
    Last Post: 02-05-2015, 11:34 AM
  3. Replies: 2
    Last Post: 04-14-2014, 10:42 PM
  4. Apostrophe in name
    By NISMOJim in forum Programming
    Replies: 1
    Last Post: 04-04-2013, 10:14 PM
  5. Replies: 5
    Last Post: 09-05-2012, 09:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums