Significant figures in access query

[gdesreu]

Does anyone have a formula or function for access 2010 that performs significant figures?
Thanks

 

[Joe4]

Can you explain in more detail exactly what you are trying to do?

 

[Micron]

Check out the Access Forums. I saw a function posted there once for determining significant figures. AFAIK, there is no Access built in function for this.

 

[gdesreu]

I found this yesterday and it seems to work (close enough) It errors if the value field is 0 but I got around that with an iif statement, and correct me if Im wrong but due to the way access handles storing a value like 23.0 as 23, this sig fig calc reports out 23 as opposed to 23.0 in a three sig fig calc. Probably a non issue for me but it might not be for others. Also, this turns the value to a string so after I get the sig fig txt string I just changed it back to a value using a formula in another column.

   Public Function FormatSigFig(Value As Double, SigFigs As Long) As String
        Dim RoundedValue As Double
        Dim Digits As Long
        Digits = SigFigs - Int(Log(Abs(Value)) / Log(10)) - 1
        FormatSigFig = Int(0.5 + Value * 10 ^ Digits) / 10 ^ Digits
    End Function

 

[gdesreu]

Can you explain in more detail exactly what you are trying to do?

Sure, I have a column of numbers, I need to export those numbers out as significant figures. (Im guessing three sig figs at this point) If anyone has a better system than the one I posted Im all ears. Thanks!

 

[Joe4]

If your values always had the same number of digits to the left of the decimal point (i.e. "2"), then you might be able to accomplish what you want with the FORMAT function. Otherwise, you would probably need to use some VBA code, like you have (or some other code).

 

[Micron]

If your values always had the same number of digits to the left of the decimal point (i.e. "2"), then you might be able to accomplish what you want with the FORMAT function. Otherwise, you would probably need to use some VBA code, like you have (or some other code).

I'm not sure it's that simple. 0 is insignificant if it's at the beginning, but not if in between significant figures (all others), and is if it is after a decimal. Maybe what you had in mind would be ok, though.

gdesreu - Access only drops a single zero after a decimal if that's the way you have it set up. In some cases, it does not drop it at all - it just doesn't show it. Your number data field has to be the correct number type and format if you want zeros after the decimal. As for your function returning a string, that's because it's designed that way ("as string").
What I find odd about it is this line FormatSigFig = Int(0.5 + Value * 10 ^ Digits) / 10 ^ Digits seems to be coercing the value to an integer, only to have the function convert it to a string. You could try changing "as string" to "as Long" or some other number type. If you are not getting the zeros after a decimal and you need them to be counted, you need a better function I think.