Need help with a data validation expression.

[NachoMan]

This should be easy for a lot of people out there.

I need to write a data validation expression that specifies that entries in a [ReportNumber] field cannot end in a letter. I have a feeling that I need to utilize the Right() function, but I'm not exactly sure how.

Is this enough information or should I explain further? Thanks for any help/hints.

 

[Mile-O]

Put this in a module:

Public Function IsAlpha(ByVal strValue As String) As Boolean
 
    Dim intCode As Integer

    intCode = Asc(Right(strValue, 1))

    Select Case intCode
        Case Is < 64, Is > 123, 91, 92, 93, 94, 95, 96
            Exit Function
        Case Else
            IsAlpha = True
    End Select

End Function

You validation rule can now be:

Not IsAlpha([MyField])


where MyField is the field you want to validate.

 

[IgorB]

If you input text box has for example name: txtMyNumber then
use VBA code like this:

Private Sub txtMyNumber_BeforeUpdate(Cancel As Integer)
If IsNumeric(Right(txtMyNumber,1)) Then
Else
MsgBox "The last character must be numeric(number)!"
Cancel=True
End if
End Sub