Validations in VBA access

Question

In a Textbox it will carry the value of a percentage with decimals but I only want to be able to capture a single decimal point and only 2 decimals without rounding, I have already looked for a thousand ways and I can't find how

currently I only lied the validation that I only accept numbers in the ON KEY PRESS event

first of all, Thanks

Answer 1

If figured out a solution using the KeyPress event. As you can see it is quite difficult to account for all possible circumstances. For instance the user could have moved the cursor to a position before the decimal point or he/she could have selected text or try to enter a decimal point in the middle of a number which would result in more decimal than allowed.

In this example I named my TextBox Number:

Private Sub Number_KeyPress(KeyAscii As Integer)
    Dim s As String
    Dim pos As Integer
    
    If InStr(Number.Text, ".") > 0 And KeyAscii = Asc(".") Then
        ' Do not allow a second decimal point
        KeyAscii = 0
    ElseIf KeyAscii >= Asc("0") And KeyAscii <= Asc("9") Then
        s = Number.Text & Chr$(KeyAscii)
        pos = InStr(s, ".")
        ' Do not allow more than 2 decimal but allow input if before decimal point.
        If pos > 0 And Len(s) - pos - Number.SelLength > 2 And Number.SelStart >= pos Then
            KeyAscii = 0
        End If
    ElseIf KeyAscii <> Asc(".") And KeyAscii <> 8 Then ' 8 = Backspace
        ' Do not allow other characters to be entered
        KeyAscii = 0
    ElseIf KeyAscii = Asc(".") And Number.SelStart < Len(Number.Text) - Number.SelLength - 2 Then
        ' Do not allow entering a decimal point if this results in more than 2 decimals
        KeyAscii = 0
    End If
End Sub