Rounding numbers in a form

[AlanW]

I know this might be quite simple for some people but I can’t figure out the code that checks the value entered in a text box on a form to ensure that it does not exceed 2 decimal places. I want to achieve this using code and not by an input mask or by changing ‘Decimal place’ to 2. (The reason being formatting issues)

Example: Amount entered £100.123 will produce a message box saying the amount entered should be limited to 2 decimal places

Thanks for any help

 

[John Big Booty]

Try the following in your text box's Before Update Event;

    If (Len(Me.FieldName) - InStrRev(Me.FieldName, ".")) > 2 Then
        MsgBox "This field must be input to no more than two decimal places"
        Cancel = True
    End If

 

[AlanW]

Works perfectly. Thanks so much.

 

[John Big Booty]

Not a problem

 

[AlanW]

Sorry John . . . Just a small problem

Now when I enter a number without a decimal point I get the error message.

Example: If I enter 250 and not 250.00 I get the error message. Any ideas?

 

[Galaxiom]

   DotLoc = InStrRev(Me.FieldName, ".")
   If DotLoc <> 0 AND (Len(Me.FieldName) - DotLoc ) > 2 Then
      etc

 

[John Big Booty]

Try;

If InStrRev(Me.FieldName, ".") = 0 then
    Exit Sub
        ElseIf (Len(Me.FieldName) - InStrRev(Me.FieldName, ".")) > 2 Then
            MsgBox "This field must be input to no more than two decimal places"
            Cancel = True
End If

 

[AlanW]

This works fine now. Thanks so much again

Private Sub Payment_Amount_BeforeUpdate(Cancel As Integer)

DotLoc = InStrRev(Payment_Amount, ".")
If DotLoc <> 0 And (Len(Me.Payment_Amount) - DotLoc) > 2 Then
MsgBox "This field must be input to no more than two decimal places"
Cancel = True
End If
End Sub