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", "")

 

[Pat Hartman]

If the "number" contains leading zeros, it is NOT a number, it is a code and codes need to be stored as text. For example, the New England zip codes all start with a leading zero. Excel automatically messes this up so be careful with import/export.

Using the BeforeUpdate event of the form is generally the best place to do validation. The IsNumeric() does accept some letters because it considers anything in scientific notation to be numeric. So, at a minimum e and d will get past that function.

Or, I would use the function posted by @MajP

 

[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.