Validation Rule - is this one possible??? 1

[Guest_imported]

Ok a chalenge to evreyone here... i need to write a validation rule so that any amonnt of letters, and numbers as well as -'s and ''s. but no other symbols can be allowed in the field. i can't quite seem to get it right. can anybody help me??? is there a solution??? please reply and let me know. Ashley

 

[PPSAG]

I'm not sure I could give you the actual code. But I believe you can write an if statement to validate with "Acceptable" characters. Include the ascii values for the letters, the - and the ". Mooo...

 

[pgk]

Hi,
Try the following:

Private Sub Text1_KeyPress(KeyAscii As Integer)
If Not ((KeyAscii > 64 And KeyAscii < 91) Or (KeyAscii > 96 And KeyAscii < 123) Or KeyAscii = 34 Or KeyAscii = 39 Or (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8) Then
MsgBox &quot;Unacceptable character&quot;
KeyAscii = 0
End If
End Sub

Watch out for any word wraps. Hope it helps. Let me know what happens.
With regards,
PGK

 

[humvie]

I am trying to alter your code so that rather than displaying a message, it automatically accepts the input without the &quot;unacceptable character&quot;.

ex. Chr(39) in O'Mally returns OMally

Any takers?

 

[flaniganmj]

Try something like this:
********************************
'
Private Sub myControl_AfterUpdate()
'
Dim i As Integer
Dim myTemp As String
'
For i = 1 To Len(Me.myControl)
'
If Asc(Mid(Me.myControl, i, 1)) > 64 And Asc(Mid(Me.myControl, i, 1)) < 91 Then
myTemp = Mid(Me.myControl, i, 1)
End If
Next i
'
Me.myControl = myTemp

End Sub

***********************************
Didn't really test it, but simply roll through each char and test for the value, build appropriate string, and re-assign value.

Let me know if that helps,

flan

 

[humvie]

Thanks Flan,

Sorry it took so long to respond to your thread but I forgot to tag it as notification and could not remember location of this thread. (phew!!)

I will give this a try and let you know.
thanks again

Humvie

 

[humvie]

Hi Flan,

I took a crack at it and what it does is ignores anything before the conditons and portions of after conditions. Let me explain with an example.

I key in &quot;O'Mally&quot; and get back &quot;M&quot;. Somehow it stripped whatever was before and after the M.

I tried a few variations but kept getting all sorts of strange combinations.

Humvie

 

[flaniganmj]

Make this change:

********************************
'
Private Sub myControl_AfterUpdate()
'
Dim i As Integer
Dim myTemp As String
'
For i = 1 To Len(Me.myControl)
'
If Asc(Mid(Me.myControl, i, 1)) > 64 And Asc(Mid(Me.myControl, i, 1)) < 91 Then
myTemp = myTemp & Mid(Me.myControl, i, 1)
End If
Next i
'
Me.myControl = myTemp

End Sub

***********************************

Also this only accepts uppercase ascii values. So it will return &quot;OM&quot; for &quot;O'Malley&quot;. Put another condition in for the lower case values. Here's a table

http://www.asciitable.com

Anyway, the values for lower case are 97 - 122.

If you need some more help, let me know.

flan