Decimal

A

Arvin Meyer [MVP]

In the table design view, set the datatype as Number, Double, Fixed format,
2 decimal places. Makes sure that Fixed, and 2 decimal places in the form
and report design view for that control.
 
K

Ken Sheridan

You can use a double precision floating point number type or, even if the
data are not currency values then using a Currency data, it being of an
underlying precision to four decimal places, is a convenient way of
protecting against cumulative rounding errors in any calculations involving
the data.

If the data are not currency values set both the Format property of the
control of the column Fixed, or Standard if you want to show commas as the
thousands separator. If the column contains currency data set its Format
property to Currency if you want it to use the currency symbol determined by
the regional settings in Windows Control Panel, or to a specific currency
format such as £#,##0.00 if you want it use the same currency symbol
regardless of the Windows regional settings.

If you wish you can prevent users entering more than two numbers beyond the
decimal point by putting the following in the KeyPress event procedure of a
control bound to the column in a form:

Dim ctrl As Control
Dim intDecPointPos

Set ctrl = Me.ActiveControl

intDecPointPos = InStr(ctrl.Text, ".")

If intDecPointPos > 0 Then
If ctrl.SelStart = intDecPointPos + 2 Then
KeyAscii = 0
End If
End If

Its not completely bullet-proof as a really perverse user could enter 3 or 4
numbers, then move the insertion point to the start and enter more numbers
followed by a decimal point. If you foresee that happening then you can
remove the additional numbers by putting the following in the control's
AfterUpdate event procedure:

Dim ctrl As Control
Dim intDecPointPos As Integer

Set ctrl = Me.ActiveControl

intDecPointPos = InStr(ctrl, ".")

If intDecPointPos > 0 Then
ctrl = Left(ctrl, intDecPointPos + 2)
End If

Note that this trims off the surplus numbers, it doesn't round the value so
whereas 123.4567 would be rounded to 123.46, the above code would make it
123.45.

Ken Sheridan
Stafford, England
 
Top