Results 1 to 8 of 8
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: Check for Illegal Chars

    I have a form that is used to search. I have a text box that the user can enter in text to search on. I want to check to make sure the user doesn't enter in a ' or a " character, as this will screw up the SQL string build in my code. Any ideas? I've tried to no avail...
    Code:
    Private Function hasIllegalChars(strText As String) As Boolean
    
    hasIllegalChars = False
    
    Dim strChar As String
    
    For i = 0 To Len(strText)
        strChar = Mid(strText, i + 1, 1)
        If strChar = "'" Then
            hasIllegalChars = True
            Exit For
        End If
    Next
    
    End Function
    Any help would be appreciated!
    Me.Geek = True

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Assuming the name of your text box for the search term is YourSearchBox

    Code:
    Private Sub YourSearchBox_Exit(Cancel As Integer)
      If InStr(Me.YourSearchBox, "'") Or InStr(Me.YourSearchBox, """") Then
        MsgBox "You Can't Include Single or Double Quotation Marks in Your Search String"
        Cancel = True
      End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Something to think about, would an user have a string with an ' as part of the search string e.g tony's etc. or other words used in abbreviation eg. it's, wasn't etc.?

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Thanks Missinglinq, your code works great!

    To address your post Poppa Smurf, that's actually why this came up. When I made this app, I forgot to check for this, and one user tried to search on a possessive noun (like Mike's) and tripped the error catching, getting the standard error message.

    Can you suggest a way to allow for these characters?
    Me.Geek = True

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    This is what I would do. First for the form, set Key Preview for the form to Yes (it's in the events tab of the form properties). Then for the text box add to the On Key Press event:

    Code:
    Private Sub txtBatch_KeyPress(KeyAscii As Integer)
    
        If (KeyAscii >= Asc("'") Or KeyAscii <= Asc("""")) Then
        
            Beep
            KeyAscii = 0
            
        End If
        
    End Sub
    When the user tries to press ' or " when entering data the system will beep at them and not allow the character.

    NOTE: My textbox is called txtBatch, you will have to substitute your textbox name.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    .... or you could escape the characters. At the end of the day, if someone wants to find O'Neil you are pretty stuffed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by DCKunkle
    This is what I would do. First for the form, set Key Preview for the form to Yes (it's in the events tab of the form properties). Then for the text box add to the On Key Press event:

    Code:
    Private Sub txtBatch_KeyPress(KeyAscii As Integer)
    
        If (KeyAscii >= Asc("'") Or KeyAscii <= Asc("""")) Then
        
            Beep
            KeyAscii = 0
            
        End If
        
    End Sub
    When the user tries to press ' or " when entering data the system will beep at them and not allow the character.
    Thanks for this DC, I wasn't able to make it work till I changed the line to read:
    Code:
    If (KeyAscii = Asc("'") Or KeyAscii = Asc("""")) Then
    Then it worked great! Else it wouldn't let me type regular letters either.

    Thanks for your help everyone.
    Me.Geek = True

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Hey pootle,
    I ended up using your post in this thread, was able to do the search now, worked great.
    Me.Geek = True

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •