Decimal Place Validation

  • Thread starter Thread starter Bob Howard
  • Start date Start date
B

Bob Howard

How can I test whether more than two decimal places are entered into an
unbound control, and issue an error message if that is the case?

The following are acceptable:
125
125. (a number followed by a decimal point but no decimal digits after it
125.3
125.36

But the following is not acceptable:
125.381 (more than 2 decimal places is in error)
125.3815 (more than 2 decimal places is in error)
etc.
etc.

Thanks!

Bob (@Martureo.Org)
 
In the BeforeUpdate event of the control, use Instr() to locate the decimal
point in the control's Text property.

This kind of thing should work unless you are expecting Scientific format,
or trailing negative/brackets:

Private Sub Text1_BeforeUpdate(Cancel As Integer)
Dim strText As String
Dim lngDecPlaces As Long
With Me.Text1
strText = Trim(.Text)
lngDecPlaces = Len(strText) - Instr(strText), ".")
If lngDecPlaces > 2 Then
Cancel = True
MsgBox "2 dec places only."
End If
End With
End Sub


BTW, it might be easier (and less frustrating to the user) to round the
result in the control's AfterUpdate event:
Private Sub Text1_AfterUpdate()
Me.Text1 = Round(Me.Text1, 2)
End Sub

Also, set the unbound control's Format property to "General Number" or
similar, so Access won't accept non-numeric values.
 
Back
Top