Number formatting problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Salutations
I am having big problems with the format of numbers in txtboxes. Here is my
situation using easy numbers: 4 txtboxes accept numerical values. Their
table has Format Property 0.000 and Decimal Places 3. A 5th txtbox shows
the sum, like the column at left. Now, I need to be able to stop workers
from entering, say 3.9999. When they do, we get the column at right.

5.000 5.000
3.999 4.000
1.000 1.000
2.000 2.000
--------- --------
11.999 12.000

I know this sounds dumb, but the 'groundrules' over which I have no control
are that ONLY 3 decimals are allowed and NO ROUNDING is to take place.
***So, entering 3.999999999999 must just give 3.999. Also, I don't mean that
just 3 decimals are displayed and the others aren't, but that the actual
value of the number is 3.999 (no other non-zero decimals, showing or not).
This is important because these values are used in subsequent calculations.

I can actually 'fake' the result using strings, but I need these values to
be numbers.
 
Hi Sophie,

One way is to put something like this in the textbox's BeforeUpdate
event:

With Me.ActiveControl
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
Cancel = True
End If
End With

If you want feedback while the user is typing rather than when they move
away from the textbox, you could use a similar test in the KeyPress or
Change event.
 
John - you're code works awesome in the BeforeUpdate event, but...

Instead of using this in 18 textboxes, Ive now been trying to put your code
in a Sub called by each each textbox's BeforeUpdate event. Something like...

Private Sub txtTestA_BeforeUpdate(Cancel As Integer)
Call CheckDecimalPlaces("txtTestA")
End Sub

Private Sub CheckDecimalPlaces(CheckControlName As String)
With Me!CheckControlName
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
Cancel = True
End If
End With
End Sub

I think the problem is that the Cancel in the second sub is not defined.
I've tried lots of remedies, but I can't seem to get the syntax right. Any
clues?

Great thanks
 
Hi Sophie,

You'll need to modify the CheckDecimalPlaces function so that it
'tells' the BeforeUpdate function whether it should cancel or not.

Something like this should work (air code):

Private Sub txtTestA_BeforeUpdate(Cancel As Integer)
Cancel = CheckDecimalPlaces("txtTestA")
End Sub

Private Function CheckDecimalPlaces(CheckControlName As String) As
Boolean
'returns true if there was a problem
CheckDecimalPlaces = False
With Me!CheckControlName
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
CheckDecimalPlaces = True
End If
End With
End Sub

Daniel
 
You can just do this:

Private Sub CheckDecimalPlaces(ByRef Cancel As Integer)
With Me.ActiveControl
If (InStr(.Value, ".") > 0) _
And (Len(.Value) - InStr(.Value, ".") > 3) Then
MsgBox "Not more than 3 decimal places in this field!", _
vbOKOnly + vbExclamation
Cancel = True
End If
End With
End Sub

and call it from each textbox's BeforeUpdate event procedure with:

CheckDecimalPlaces Cancel
 
Back
Top