check decimal and if numeric

[johnboy7676]

I have an unbound textbox, txtAmount. I want to make sure that user
enters only two decimal places, and that the input is numeric.

I tried using InputMask 99999999.99 which works for positive numbers,
but that mask won't allow the minus sign (or plus sign) to be used.
So, how can I allow input of a negative number? Currently, if
TransactType is "Payment", it takes the value in Amount * -1 (if
existing Amount > 0), so that effectively makes it a negative. But, a
TransactType of "Adjustment" could be either negative or positive. I
could make a TransactType of "Adjustment-negative" and
"Adjustment-positive", but there will probably be additional
TransactTypes added later, so think that might get me into trouble.

I can use If Not IsNumeric to check for numeric, but then I can't
check (or just don't know how) for number of decimal places.

Any suggestions? Thanks
John

 

[Allen Browne]

Personally, I hate input masks. There's probably a better way.

From your description, it seems that you want to make sure that:
a) the value is a valid number, and
b) the value has no more than 2 significant digits after the decimal.

For (a), set the text box's Format property to a numeric value, such as
Fixed. Set Decimal Places to 2 if you want to always show the 2 places.
Alternatives might be to use General Number (if the 2 places are really
optional) or Currency (if that makes cents.)

For (b), use the AfterUpdate event procedure of the text box to round the
value. This kind of thing:
Private Sub Text0_AfterUpdate()
Me.Text0 = Round(Me.Text0, 2)
End Sub

 

[John]

I set the Format property as you suggested. That did prevent text from being
entered. And yes, that makes "cents"..-)

As for second suggestion, if for example, they input 25.228 instead of 25.22
then it would round to 25.23 when should be 25.22. And, if truncate at two
decimals, who's to say the third or fourth digit was the one that was in error?
Maybe the first digit after the decimal was the mistake.

I found something while searching google, it was suggested to use a Validation
Rule of :
=int(myfield*100)/100
which seems to work. I'm not sure why, but it does seem to work. (originally
posted by "Turtle")

Thanks, lots of good info on your web site, btw.

 

[Allen Browne]

Okay, you have it solved.

Int() rounds down, so effectively truncates an subsequent digits for
positive numbers. It's different when you round negative numbers down, e.g.:
- $2.281
would round down to:
- $2.29

If you don't want that behavior, use Fix() instead of Int().