Solved Restricting Entry to Numbers Only in Text Field

[RogerCooper]

I have a field that should only have numbers as characters, but it can have a leading zero, so I can't use a numeric field. Is there any way that I can prevent accidental entry of non-numbers in the field at the database level?

 

[theDBguy]

Hmm, at the table level, you could try using the Validation Rule property. Other possible options are the Format and Input Mask properties.

 

[RogerCooper]

This worked in validation

IsNumeric([Entry#])=True

 

[ebs17]

but it can have a leading zero

Is there a rule for this?

One can also store numbers as numbers in a number field and display them formatted:

SELECT NumberField, Format(NumberField, "0000") AS NumberFormat FROM TableX

 

[RogerCooper]

The length of the values can vary.

Even if the values had a fixed length, I would be uncomfortable using a numeric field with something that is not really a number. It could cause future problems if somebody thought is was really number and didn't pad with leading zeros.

 

[MajP]

If you like to alert them as soon as they type an illegal character instead of waiting to the end

Private Sub AlertNonNumeric(ctl As Access.Control)
  If Not IsNumeric(ctl.Text) Then
    MsgBox "Only numeric characters allowed"
      ctl.Value = Left(ctl.Text, Len(ctl.Text) - 1)
      ctl.SelStart = Len(ctl.Text)
  End If
End Sub

Private Sub Text0_Change()
  AlertNonNumeric Text0
End Sub

 

[ebs17]

Bad character: Is the decimal delimiter a bad character?

? IsNumeric("0045.67"),  IsNumeric("0045.")
Wahr          Wahr

 

[moke123]

if decimal is an issue

Private Sub Text0_KeyPress(KeyAscii As Integer)

    AllowOnlyNumbers KeyAscii, Me.Text0

End Sub


Public Sub AllowOnlyNumbers(ByRef KeyAscii As Integer, ctl As TextBox)


    Select Case KeyAscii

        Case 48 To 57           '0 to 9
            'do nothing
        Case 46                 'comment out if a "." is not needed  (ie. 2.1)
            'do nothing


        Case Else
      
            MsgBox "Only Numbers Permitted"
            ctl.SetFocus
            ctl.SelStart = Len(ctl.Text)
            KeyAscii = 0
    
    End Select
    
End Sub

 

[ebs17]

Regular expressions could also be used for processing. The following abbreviated statement deletes all non-digits.

AnyValue = RegExReplace(AnyValue, "\D", "")

 

[sxschech]

In reference to the code for RegExReplace, it can be found here under post #15:

Regex

Hi, How can i calculate this in Via? 2(2A2B)= 4A 4B And 2(2A2B) = 8 Tk u

www.access-programmers.co.uk

 

[RogerCooper]

Bad character: Is the decimal delimiter a bad character?

? IsNumeric("0045.67"),  IsNumeric("0045.")
Wahr          Wahr

A period would be a bad character but in practice that it not likely to be an issue. The problem is inconsistencies in the source document being transcribed, which sometimes has dashes and spaces.