Checking a string for Characters

[txgeekgirl]

The longer I am alive - the more assured I am that my job is not as developer but as stupidity catcher. I think in the past 6 months I have written more code to stop the actual ability of one to be stupid as an end user than working on my fun projects.

Here we go:

I have a simple quick and dirty DB in Access / VBA which allows scanners to track what they've said they've scanned. You choose a program, input a case number and how many pages you've scanned by section and the date.

Problem - they are trying to use alpha characters in the the case number in more than the three situations where characters are allowed. Here is what I have so far:

Sub CheckCasenum()
    If Me.Type <> "MHCNMID" Or Me.Type <> "MHCNOD" Or Me.Type <>"MHCNFR" Then
       IsClean (Me.CaseNotxt)
       MsgBox Me.CaseNotxt
    End If
End Sub

Public Function IsClean(strToCheck) As Boolean
    Dim lng As Long
    Dim CleanMe As Boolean
    If Len(strToCheck) > 0 Then
    For lng = 1 To Len(strToCheck)
    Select Case Asc(Mid$(strToCheck, lng, 1))
     ' Numbers 0 to 9
    Case 48 To 57
 
    ' Letters A to Z
    Case 65 To 90
    ' Letters a to z
    Case 97 To 122
 
    Case Else
        Clean = True
        Exit For
        End Select
    Next lng
    End If
IsClean = Not CleanMe
MsgBox CStr(CleanMe)
End Function

I have no idea how it's suppose to work - other than read the ascii val of a num/letter and say what's allowable.

 

[Steve R.]

I am not fully following what you are attempting to accomplish. I assume that you want only certain standard entries such as "MHCNOD". If that is the case what I would recommend is the use of a table where each of the standard entries is entered. The use of the table makes it easy to modify your code when the standard entries change.

To test for a valid entry you can have the user select from a drop-down list where "limit to list" is true. If they attempt to make a "bad" entry, it will be rejected.

If you are trying to limit the characters entered to only numbers (/ for dates), here is the code that I am using.

Private Sub Text17_KeyDown(KeyCode As Integer, Shift As Integer)
    Rem Debug.Print "ASCII Code: ";KeyCode
    Select Case KeyCode
        Case 8 'Back Space
            Rem OK
        Case 9 'Tab Key
            Rem OK
        Case 13 'Return Key
            Rem OK
        Case 27 'Escape key
            Rem OK
        Case 37 'Left Arrow
            Rem ok
        Case 39 'Right Arrow
            Rem ok
        Case 46 'Delete Key
            Rem OK
        Case 48 To 57 'Keyboard Numbers 0-9
            Rem OK
        Case 96 To 105 'Num Pad Numbers 0-9
            Rem OK
        Case 191 '/////////
            Rem OK
        Case Else
            DoCmd.CancelEvent
    End Select
End Sub

 

[txgeekgirl]

Hey Steve - Yeah - that is way off. The only time we allow characters in a case number when logging EMR archiving is Contact Notes and Crisis - so it can have 2004C Any patient in this status in the year 2004 with the last name starting in C. The rest of the time its a 3-10 digit case number.

 

[txgeekgirl]

Here is how I fixed it. Actually my boss' idea to change the InputMask on the Type definition.

Sub TypeCB_AfterUpdate()
    If Me.TypeCB = "MHCNMID" Or Me.TypeCB = "MHCNOD" Or Me.TypeCB = "MHCNFR" Or Me.TypeCB = "MHCRMID" Or Me.TypeCB = "MHCROD" Or Me.TypeCB = "MHCRFR" Then
        Me.CaseNotxt.InputMask = xxxxxxxx
    Else
        Me.CaseNotxt.InputMask = 99999999
        MsgBox "This Type only allows numeric Case Numbers.  Text in the Case Number will not be allowed."
    End If

End Sub