--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Larry G." <Lar...@discussions.microsoft.com> wrote in message
news:5DFB568B-882A-4CEA...@microsoft.com...
>I posted this question last week and for the FIRST and hopefully last time,
>I
> did not receive ANY responses! So here is the question again:
>
> Is there a way to set significant figures in Access. I would like to set
> two
> significant figures in some numerical data, so that fifty will display as
> "50" two significant figures, and five will display as 5.0 (also two S.F.)
> Does anyone have any idea how to do this?
>
> Larry
>
>I posted this question last week and for the FIRST and hopefully last time, I
>did not receive ANY responses! So here is the question again:
>
>Is there a way to set significant figures in Access. I would like to set two
>significant figures in some numerical data, so that fifty will display as
>"50" two significant figures, and five will display as 5.0 (also two S.F.)
>Does anyone have any idea how to do this?
I saw the question, couldn't come up with any reasonable way to answer
it, so didn't reply.
Question: is the number 5000 accurate to one, two, three, or four
significant figures? How could you tell? Would you need to go to
scientific notation (5E3, 5.0E3, 5.00E3, 5.000E3)? If so - Access will
not let you make that distinction *except* by storing the data in a
Text field and translating it to number as needed.
John W. Vinson[MVP]
>I was afraid of that. Maybe I can make a suggestion to the Access development
>team to find a way to add this to the next incarnation. It seems to me that
>people who need to use Access for scientific data would be more interested in
>significant digits rather than decimal places.
More commonly (at least in the scientific numeric data I've used), it
might make more sense to have two numeric fields: value and standard
deviation. Significant digits is a coarse measure, assuming that the
accuracy must be plus or minus a factor of ten; the uncertainty may be
known more accurately, or - even if it isn't - stating "10.5 +/- 0.1"
conveys the information clearly.
John W. Vinson[MVP]
In your case, you want 2 digits, and I see nothing stopping you from using a
format command to achieve this goal.
If you look at a text box control, you can set the number of decimals to 2
places. And, further, since you are only using 2 significant digits, then
you should use a currency data type..as that will prevent rounding errors.
So, for reports and display on the screen, you will get
5 will give you 5.00
5.5 will give you 5.50
So, it is a common need, and a everyday occurrence in the business
environment for payroll, tax calculations to restrict the calculations to
the nearest penny.
So, it is quite common that we do restrict the number of significant digits
here.
It is not clear where/when you need these restrictions (such as during data
entry...or in fact some calculations). However, if you restrict the data
entry to 2 significant digits..then additions, and most calculations thus
also restrict to 2 digits.
You going to have to explain what you are doing here...as ms-access is MOST
popular in business applications..and we restrict the rounding, and number
of digits to 2 decimal places ALL THE TIME.
Are you having a particular problem right now?
Note the following:
Public Sub TestAdd()
Dim MyNumber As Single
Dim i As Integer
For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub
Here is the actual outpput of the above:
1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1
You can see that after just 7 addtions..already rounding is occuring
and if we add the follwing line of code to the end of the above:
if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif
The above will actuall produce:
the number is something else
If you use a data type of currency, then NO rounding or errors will occur.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
http://www.members.shaw.ca/AlbertKallal
'Start Module code-----
Public Function SetSF(dblX As Double, intSF As Integer) As Double
Dim dblMantissa As Double
Dim intExponent As Integer
Dim dblSP As Double
dblMantissa = Log(dblX) / Log(10#)
intExponent = Int(dblMantissa)
dblMantissa = dblMantissa - intExponent
dblSP = 10 ^ dblMantissa
dblSP = Round(dblSP, intSF - 1)
SetSF = dblSP * 10 ^ intExponent
End Function
Public Function Round(varIn As Variant, intPlaces As Integer) As Variant
Round = Int(10 ^ intPlaces * varIn + 0.5) / 10 ^ intPlaces
End Function
'End Module code-------
Examples:
SetSF(322222, 3) = 322000
SetSF(777777, 1) = 800000
SetSF(0.0000015, 1) = 0.000002
SetSF(2.048004, 2) = 2
This should work for reasonably behaved input. Note that the final
example would be better as 2.0. A cure of returning a dynamically
formatted string seems worse than the disease. Perhaps the IIF function
can weed out this instance in addition to something like
SetSF(12.048004, 3) which returns 12. There may be other pathological
situations as well.
James A. Fortune
MPAP...@FortuneJames.com