Change calculated field result in a query to 3 significant figures

[chromachem]

We have a query that we look at in datasheet view. Fill in some known values into a few fields, and a calculated result based on the entered data shows in the RESULTS field. We would like to have the calculated field end up displaying the result of the calculation in 3 significant digits.



How do I go about changing the field that contains the calculated result to 3 sig figs?

[pbaldy]

Would the Round() function work for you?

[chromachem]

Round() does bankers rounding. I have found a function that can handle that aspect of rounding. I think the best way to explain the problem would be a few examples.

Calculated Result	Result in 3 sig figs
0.167857899	0.168
37.23789357	37.2
695.8390326	696
8924.258434	8920
78935.36836	78900
678238.9235	678000

We accomplished this in excell vba with a function and a subroutine.

Function SigRnd(ByVal d As Double, n As Integer) As Double    ' shg 2008-09
    ' Rounds d to n significant digits
    Const ln10  As Double = 2.30258509299405
    Dim dSgn    As Double
 
    If d <> 0 Then
        If n < 1 Then n = 1
        If n > 16 Then n = 16    ' max precision of double
        dSgn = Sgn(d)
        If d < 0 Then d = -d
        SigRnd = dSgn * WorksheetFunction.Round(d, n - 1 - Int(Log(d) / ln10))
    End If
End Function

Sub SigFig()'
' SigFig Macro
' Macro recorded 12/31/2004 by Norman Numnutts


' Use function SigRnd to put digits into sig figs.
If IsNumeric(ActiveCell) Then
ActiveCell = SigRnd(ActiveCell, 3)
Select Case ActiveCell
Case Is < 1
Selection.NumberFormat = "0.000"
Case Is < 10
Selection.NumberFormat = "0.00"
Case Is < 100
Selection.NumberFormat = "0.0"
Case Is < 1000
Selection.NumberFormat = "0"
Case Is < 10000
Selection.NumberFormat = "00"
Case Is < 100000
Selection.NumberFormat = "000"
Case Is < 1000000
Selection.NumberFormat = "0000"
End Select
End If
If ActiveSheet.Name = "524" And ActiveCell < 1 Or ActiveSheet.Name = "524 DEP" And ActiveCell < 1 Or ActiveSheet.Name = "524 DEP Sub" And ActiveCell < 1 Or ActiveSheet.Name = "EDEP" And ActiveCell < 1 Or ActiveSheet.Name = "THM DEP" And ActiveCell < 1 Or ActiveSheet.Name = "THM DEP SUB" And ActiveCell < 1 Or ActiveSheet.Name = "Vol Log" And ActiveCell < 1 Or ActiveSheet.Name = "PCE" And ActiveCell < 1 Then
Selection.NumberFormat = "0.00"
End If


End Sub

The number format in excel vba allowed us to deal with 0's when there is no decimal point.

So...

We have a calculated field [Result] in a table. Based on values entered in a form we get the value for the [Result] field.

Please bear with me as I am new to Access.

First question: Do I even want the [Result] field in the table to be a calculated data type, or should it be something like text. Take a variable from the form (call it txtResults), put it in sigfigs, and stick it in the [Result] of the table.

Second question: How the heck can I get those pesky 0's to show up when numbers are larger than 1000??

Thanks

[Thompyt]

use the Format function?

[ssanfu]

First question: Do I even want the [Result] field in the table to be a calculated data type, or should it be something like text. Take a variable from the form (call it txtResults), put it in sigfigs, and stick it in the [Result] of the table.

I would not have a calculated field.

Second question: How the heck can I get those pesky 0's to show up when numbers are larger than 1000??

Seems to be there automatically...???

Is this what you want??

[chromachem]

Awesome. I was hung up on the implementation. Thx!