Solved Restrict Decimal Places In Control Entering Data & Selection (1 Viewer)

[dalski]

I'm having to work to many decimal places in my SQL Server BE & want the user only to have to enter 4 decimal places in the control. Much higher precision is needed for calculations... & the calc can populate the data in the BE which is fine. Restricting what the user sees is easy with the format properties but when clicking on the control & the stored amount is > 4 decimal places the user is burdened with many decimal places which is horrible for entering data...

Applying the format to the field in the table design in Access (which is actually based on a SQL Server View in this case) surprisingly also does not have an effect.

What is the trick to restrict the amount of dp's on data-entry & selection? An input mask is not pretty nor does the decimal place restriction of #,###.#### work. It also makes it horrible then wasting space on the integral resulting in huge control sizes which I cannot have.

 

[The_Doc_Man]

I believe you are using an Access FE. If so, while that data-input control has focus, you are allowed to use a property that ONLY applies during in-focus events. The control.Text property tells you the actual characters entered into the control, the text values against which the format and type conversion (converting text to a number) will occur. Note that once you fire (well, when your form fires) the .LostFocus event, the .Text property of the control in question is no longer available. Access can (and DOES) make certain properties "vanish" based on form state after/due to events.

Some people use the .Change event and re-evaluate the input on each keystroke. Which you could do as long as the evaluation code for that event stays relatively small. Large enough to detect the problem but otherwise not very big - and if you DO detect an issue, the size of the "chastise the user" code isn't as significant.

 

[dalski]

Very nice solution, thank you Doc, this isn't well documented at all, an expert technique from you there I think, for others I used below. I left the change event so the user can enter more than 4 dp's if desired but is not burdened by many dp's on selection.

Private Sub CtrlName_GotFocus()
  Me.CtrlName.Text = Round(Me.CtrlName.Value, 4)
End Sub

 

[theDBguy]

Does the Input Mask for this? Just thinking out loud...

 

[The_Doc_Man]

The code you showed might work due to LET-Coercion but is TECHNICALLY incorrect in that you are using ROUND (a numeric function) to feed the control.Text property (which is not numeric but is text). You said you were not satisfied with input masks. There are things you can do using, for example, InStrRev to test how many characters exist to the right of the decimal point by examining the .Text property of the control.

InStrRev function (Visual Basic for Applications)

VBA language reference

learn.microsoft.com

IF InStrRev( Trim$(control.Text), ".", -1 ) > 4 Then
complain
Else
do nothing
End If