Number Format - Significant Figures 2

[gemoon]

Im trying to set the number format for a table column. The data that goes into the column has a range of decimal formats that i would like to preserve. I currently have the field size set to "double", the format to "general", and the decimal places to "auto".

If the last digit is a number other than zero, this setup works fine. However if the last digit(s) is zero, the significance is lost. For example if i have the numbers 3.1200, 3.120, and 3.12, i would like them to be stored and displayed as 3.1200, 3.120 and 3.12. However all 3, are rounded to 3.12.

Ive played with several other format combinations, but have had no luck. Storing the data as text is an option, however, this may cause problems down the road when I do statistics on this data.

Is there a solution to this problem?

Any suggestoins are greatly appreciated.

gemoon

 

[RickSpr]

I understand your problem, but I don't think Access or VBA has any way to handle significant trailing zeros.

You might try using text, but I think a better solution would be to store a "precision" value that you derive from the entered data. You'll determine the value by counting fraction digits when the value is entered, and you'll use it to generate a Format property that shows that many fraction digits.

First, you need to add a Precision field to the table, to hold this new datum. Make it an Integer field with a Default value of 0.

On your form, create a text box bound to the Precision field. If you prefer, you can make it invisible. I'll assume its name is "txtPrecision".

Next, you'll need a function that returns a Format string with the appropriate number of fractional digits. Add this to the form's code module:

Private Function PrecFormat(NumDigits As Integer)
    If NumDigits = 0 Then
        PrecFormat = "#"
    Else
        PrecFormat = "#." & String$(NumDigits, "0")
    End If
End Function

You'll need to adjust the Format property of your data input field every time you display a record. You do this in the form's Current event procedure. Let's say your input field is a text box called "txtValue". The code for the event procedure would then be:

Private Sub Form_Current()
    [txtValue].Format = PrecFormat([txtPrecision])
End Sub

When data is entered in the field, you'll need to count how many digits follow the decimal point and save it in the Precision field. You'll also have to change the Format to match the number of digits they entered. You do this in txtValue's AfterUpdate event procedure:

Private Sub txtValue_AfterUpdate()
    Dim i As Integer

    If IsNull([txtValue]) Then
        i = 0
    Else
        i = Instr([txtValue].Text, ".")
        If i <> 0 Then i = Len([txtValue].Text) - i
    End if
    [txtPrecision] = i
    [txtValue].Format = PrecFormat(i)
End Sub

Note: The IsNull test allows for the possibility that the field is erased. The lines following that allow for a value with no decimal point.

That should pretty well cover it, I think. The beauty of this method is that your data value is still stored in a Single field in the table, so you won't have to do anything special to calculate statistics with it. Rick Sprague

 

[gemoon]

rick,
thanks. that is a nice solution.

gemoon