Input mask?

[Leslie Isaacs]

Hello All

I have a text field on a form where the value must be either empty (=null)
or it must be exactly 7 alphas all of which must be either "Y" or "N".
Is there an input mask to ensure this, or do I need code? I know I could do
something along the lines of

If Mid([myText],1,1) <>"Y" and If Mid([myText],1,1) <>"N"
or
If Mid([myText],2,1) <>"Y" and If Mid([myText],2,1) <>"N"
etc
Then MsgBox("Invalid entry")
End If

.... but that seems very longwinded. Is there a better way?

Hope someone can help.
Many thanks
Leslie Isaacs

 

[Douglas J. Steele]

Function ValidateYesNo(TextValue As Variant) As Boolean
Dim booOK As Boolean
Dim lngLoop As Long
Dim strCurrChar As String

booOK = True

If IsNull(TextValue) = False Then
For lngLoop = 1 To 7
strCurrChar = Mid(TextValue, lngLoop, 1)
If strCurrChar <> "Y" And strCurrChar <> "N" Then
booOK = False
Exit For
End If
Next lngLoop
End If

ValidateYesNo = booOK

End Function

 

[Leslie Isaacs]

Douglas

Brilliant - your code is obviously much neater than any attempt of mine
would have been, and I can now use ValidateYesNo as the validation rule!

Many thanks for your help.
Les

Function ValidateYesNo(TextValue As Variant) As Boolean
Dim booOK As Boolean
Dim lngLoop As Long
Dim strCurrChar As String

booOK = True

If IsNull(TextValue) = False Then
For lngLoop = 1 To 7
strCurrChar = Mid(TextValue, lngLoop, 1)
If strCurrChar <> "Y" And strCurrChar <> "N" Then
booOK = False
Exit For
End If
Next lngLoop
End If

ValidateYesNo = booOK

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello All

I have a text field on a form where the value must be either empty (=null)
or it must be exactly 7 alphas all of which must be either "Y" or "N".
Is there an input mask to ensure this, or do I need code? I know I could
do
something along the lines of

If Mid([myText],1,1) <>"Y" and If Mid([myText],1,1) <>"N"
or
If Mid([myText],2,1) <>"Y" and If Mid([myText],2,1) <>"N"
etc
Then MsgBox("Invalid entry")
End If

... but that seems very longwinded. Is there a better way?

Hope someone can help.
Many thanks
Leslie Isaacs