Results 1 to 4 of 4
  1. #1
    Puebles is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Lansing, MI
    Posts
    14

    InStr() and quotation marks

    I have a bit of code that parses the comments in a table to determine if the comment includes one of 32 preset words or phrases. I received an error because one of the words contained a single quote: Rec'd. So, I added the IF statement (InStr(1, rs5![ExcludeThisWord], "'") = 0) to skip that words with a single quote.



    Code:
    Do While Not rs5.EOF
        If InStr(1, rs5![ExcludeThisWord], "'") = 0 Then
            strExcludeThisWord = rs5![ExcludeThisWord]
            strExcludeThese = " UPDATE tblComments SET tblComments.ExcludeMe = IIf(InStr(1,[comment],'" & strExcludeThisWord & "'),[ExcludeMe]+1,[ExcludeMe]) WHERE (((tblComments.AFS_NUMBER)>1)); "
            db.Execute strExcludeThese, dbFailOnError
        End If
        rs5.MoveNext
    Loop
    Because the table with words and phrases is maintained by users I wanted to protect against other errors. When I added this to the IF statement ( InStr(1, rs5![ExcludeThisWord], '"') = 0) I get an error. This is using a double quote bracketed by single quotes. Both clauses work in the Access query designer but both do not work in VBA.

    Is there a way to get this to work?
    Are there other characters I need to be concerned about? I assume it is just the quotation marks because they are used by the command/function.

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Try

    (InStr(1, ExcludeThisWord, """") = 0)


  3. #3
    Puebles is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Location
    Lansing, MI
    Posts
    14
    Please explain.....4 quotes ("""") seems like it would search for 2 quotes ("") not a single quote ("). However, this works and 3 quotes (""") does not.

  4. #4
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Hope this will do better explanation than I would.

    http://allenbrowne.com/casu-17.html

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

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2013, 04:59 AM
  2. Including Quotation Marks in SQL Server Views
    By EddieN1 in forum SQL Server
    Replies: 0
    Last Post: 02-17-2013, 07:32 PM
  3. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  4. Concatenate Quotation Marks
    By millerdav99 in forum Programming
    Replies: 3
    Last Post: 04-11-2011, 09:36 AM
  5. Transferring text within quotation marks
    By Lynette in forum Access
    Replies: 6
    Last Post: 11-24-2010, 11:58 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