Validation Rule Help

[Sinclair]

Hi,
I'm really struggling to try and find a solution to a problem.

I need to find a validation rule that only accepts:
letters, spaces and hyphens (-) in the field.

I have tried for at least 14 hours today and most of yesterday afternoon, but I just can't get it.

The furthest I've got is rejecting everything except from letters and spaces, with each these codes individually:
(I've only listed a few so you could see just a fraction of what I'd already tried)

Is Null Or Not Like "*[!((a-z) or (Chr(32),Chr(45)))]*"
Is Null Or Not Like "*[!((a-z) or (Chr(32)) or (Chr(45)))]*"
Is Null Or Not Like "*[!((a-z) and (Chr(32)) and (Chr(45)))]*"
Is Null OR Not Like "*[!((a-z) or ((\ ),(\-)))]*"
Is Null OR Not Like "*[!((a-z) or (\ ) or (\-)))]*"
Is Null OR Not Like "*[!((a-z) and (\ ) and (\-)))]*"
Is Null Or Not Like "*[!((a-z),\ ,\-)]*"

However, it is also rejecting hyphens, which I need it to accept.

Also, unfortunately it is accepting brackets

Hopefully someone here will have a solution, because I'm stumped,

James

 

[KenHigg]

I think your best bet is to write a function to do this. Can you do that or would you like me to take a stab at it?

 

[Sinclair]

I think your best bet is to write a function to do this. Can you do that or would you like me to take a stab at it?

Really? That would be great. Thanks. It's just that I'm truly terrible at using Access. I'm more of an Excel man myself - it's just that the thing I'm working on NEEDS to be done in Access.

I would be really gratefull if you could create a function which would get this to work.

James

 

[KenHigg]

See if this makes sense. An option would be to call it from a different place, like maybe from the text box exit event...(?)

Public Function myTest(myStr As String) As Boolean

Dim intX As Integer

Dim intY As Integer

intX = 1

Do While intX <= Len(myStr)

    myTest = False
    
    intY = Asc(Mid(myStr, intX, 1))

    If intY >= 65 And intY < 90 Then
        myTest = True
    End If

    If intY >= 97 And intY < 122 Then
        myTest = True
    End If

    If intY = 32 Then
        myTest = True
    End If

    If intY = 45 Then
        myTest = True
    End If

    If myTest = False Then
        Exit Do
    End If
    
    intX = intX + 1
    
Loop

End Function

Private Sub myField_BeforeUpdate(Cancel As Integer)
If myTest(Me.myField) = False Then
    MsgBox "Invalid Field Value"
    Me.Undo
End If
End Sub

 

[namliam]

You can use the "On key press" event...

In that function do something like Ken gave you but for each character beeing entered at the time.... You can alert the user as they are entering data, rather than when they finished the data entry.

 

[KenHigg]

Yeah... Then if the user pressed an incorrect key could the onkey simply would look like it ignored it - ?

 

[namliam]

Ignoring it is one option... another would be to beep the user with a message box... but that can become painfull real fast.

 

[KenHigg]

(Sorry, my AM grammer is acting up - )

 

[Sinclair]

Thanks Ken,

Am I right in putting this as an After Update Event Procedure at Form level?

 

[KenHigg]

In the sample as posted it looks like you have it where I used it, in the before update event. (At the bottom of your screen.)

 

[Sinclair]

Keeping it how it is in the screenshot, the field now accepts anything. Numbers, brackets, full stops, @ signs etc. Is this a problem in the code or just the position I've put it in?

 

[KenHigg]

See if you can make sense of this...

 

[Sinclair]

Hmmm... I tried it again, but it is still allowing other characters and numbers. I attached my .mdb to see if you could check where I've gone wrong.

 

[KenHigg]

What field are you trying to apply this rule to?

 

[Sinclair]

Sorry, I should've mentioned that. I'm applying it to the Surname field.

 

[raskew]

Small correction:

? chr(90)
Z
? chr(122)
z

If intY >= 65 And intY <= 90 Then
        myTest = True
    End If

    If intY >= 97 And intY <= 122 Then
        myTest = True
    End If

Bob

 

[Sinclair]

Small correction:

? chr(90)
Z
? chr(122)
z

If intY >= 65 And intY <= 90 Then
        myTest = True
    End If

    If intY >= 97 And intY <= 122 Then
        myTest = True
    End If

Bob

Just tried it, still allows numbers. I'm beginning to think that this task is impossible...

 

[raskew]

OK - Let's try this a different way.

Copy these three functions to a standard module, then test function SaveAlpha() as shown below.

SaveAlpha() removes non-alpha characters except as written it will retain " " and "-"

IsAlpha() tests if a character is alpha (a - z, or A - Z)

OneSpace() replaces multiple spaces with a single space.

Function SaveAlpha(pstr As String) As String
'*******************************************
'Purpose:   Removes non-alpha characters from
'           a string
'Coded by:  raskew
'Calls:     Function IsAlpha()
'           Function OneSpace()
'Inputs:    ? SaveAlpha("THE / (QUICK)  %$$ 123 - 567 Brown/ FOX")
'Output:    THE QUICK - Brown FOX
'*******************************************

Dim strHold As String
Dim intLen As Integer
Dim n As Integer

    strHold = Trim(pstr)
    intLen = Len(strHold)
    n = 1
    Do
       If Mid(strHold, n, 1) <> " " And Mid(strHold, n, 1) <> "-" And Not IsAlpha(Mid(strHold, n, 1)) Then
          strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
          n = n - 1
       End If
       n = n + 1
    Loop Until Mid(strHold, n, 1) = ""
    SaveAlpha = OneSpace(strHold)
    
End Function

Function IsAlpha(strIn As String) As Boolean
'*******************************************
'Purpose:   Determine if a character is alpha
'           i.e. "a" - "z" or "A" - "Z"
'Coded by:  raskew
'Inputs:    ? IsAlpha("4"),
'Output:    False
'*******************************************

Dim i As Integer

    i = Switch(Asc(strIn) > 122 Or Asc(strIn) < 65, 1, _
        InStr("91 92 93 94 95 96", Asc(strIn)) > 0, 2, _
        True, 3)
    IsAlpha = IIf(i = 3, True, False)

End Function

Function OneSpace(pstr As String) As String

'*******************************************
'Purpose:   Removes excess spaces from a string
'Input:     ? onespace(" now    is  the  time for   all good men  ")
'Output:    "now is the time for all good men"
'*******************************************

Dim strHold As String
    strHold = RTrim(pstr)
    Do While InStr(strHold, "  ") > 0
      strHold = Left(strHold, InStr(strHold, "  ") - 1) & Mid(strHold, InStr(strHold, "  ") + 1)
    Loop
    OneSpace = Trim(strHold)
    
End Function

If, for whatever reason, this does not remove the unwanted characters, please post an example of the string that fails to correctly process.

HTH - Bob

 

[namliam]

Check out your DB (attached) and check the "On Key Press" event...

It works You might have to expand a bit on it or change it... but it works