Number Format Problem

[BruceM]

I placed "significant digit" in quotes to indicate a phrase taken from
somebody else's words. I remember significant digits from science classes.
While I do not understand their importance, I realize that they are indeed
important.
I suggested a way of dealing with significant digits that does not force
users to type anything more than is necessary, that is simple to implement,
and that works with number fields. I also suggested using the Val function
to perform mathematical operations on text fields. I do not know the
limitations of performing math directly on text fields, but I expect it
could become a problem in some cases.

 

[James A. Fortune]

I placed "significant digit" in quotes to indicate a phrase taken from
somebody else's words. I remember significant digits from science classes.
While I do not understand their importance, I realize that they are indeed
important.
I suggested a way of dealing with significant digits that does not force
users to type anything more than is necessary, that is simple to implement,
and that works with number fields. I also suggested using the Val function
to perform mathematical operations on text fields. I do not know the
limitations of performing math directly on text fields, but I expect it
could become a problem in some cases.

I posted some code in this NG that goes a long way toward implementing
significant digits but I didn't follow through on a few problems that
remained:

http://groups.google.com/group/microsoft.public.access/msg/e055dfc42ffece74

James A. Fortune
(e-mail address removed)

 

[BruceM]

I guess I do not understand the issue. I posted code that seems to take
care of the problem as described, but the responses so far have ignored it.
I wish I knew in what way my code fails to address the situation. Perhaps
it is because it only addresses the display, not the way the numbers are
stored.
I looked at your code, which is of course quite a bit more complex than
mine, and which frankly I do not understand fully. There is more going on
here than I had realized.

 

[James A. Fortune]

I guess I do not understand the issue. I posted code that seems to take
care of the problem as described, but the responses so far have ignored it.
I wish I knew in what way my code fails to address the situation. Perhaps
it is because it only addresses the display, not the way the numbers are
stored.
I looked at your code, which is of course quite a bit more complex than
mine, and which frankly I do not understand fully. There is more going on
here than I had realized.

BruceM,

I don't think the OP ignored your post. The OP is trying to display
numbers in such a way that the number of significant figures is
displayed correctly. The code I posted was for converting numbers to a
given number of significant figures rather than for formatting them, but
the numbers returned from the function in the post, except for a few
cases, display the correct number of significant figures without using
strings. I also made a suggestion then for dealing with the exact
problem now faced by the OP due to the way Access displays numbers.
I'll revisit this problem over the holiday weekend to see if I get any
new insights. I'm trying to obtain a result that works for the general
case. Also, I tried not to limit the application of the function to
textboxes on forms. I didn't mean my answer to imply that you hadn't
answered the question. BTW, let me know if you need an explanation of
how the code works.

James A. Fortune
(e-mail address removed)

 

[BruceM]

I appreciate hearing back from you. Since I don't know what a logarithm is
or how to use one, I doubt I can fully understand the code. The carat
character also puzzles me. If I had to guess it would be that it means "to
the __ power". I realize too that I do not know what is to happen if, for
instance, the number 10.1 is entered in ten records, then those records are
added together. The numeral "10" would be displayed for each record with my
code, but the result of those records added together would be 101 rather
than 100. The more I understand just what is needed the less I imagine my
code would be of any real help. In any case it can't account for things
like 0.00024.

 

[James A. Fortune]

I appreciate hearing back from you. Since I don't know what a logarithm is
or how to use one, I doubt I can fully understand the code. The carat
character also puzzles me. If I had to guess it would be that it means "to
the __ power". I realize too that I do not know what is to happen if, for
instance, the number 10.1 is entered in ten records, then those records are
added together. The numeral "10" would be displayed for each record with my
code, but the result of those records added together would be 101 rather
than 100. The more I understand just what is needed the less I imagine my
code would be of any real help. In any case it can't account for things
like 0.00024.

I've almost got it worked out. It's possible to use the SetSF function
and to create a string for display purposes only that detects and
corrects the fact that Access wants to help us by removing the tenth's
place. Part of the correction is detecting when SetSF returns an
integer (such as 1, 12 or 320000). Another part of the detection is
comparing something like Len(CStr(SetSF([Input], intPlaces))) with
intPlaces. That form doesn't quite handle when the period should or
should not be present. An IIf function can display the output normally
when the correction is not needed. I'll try to set it up as something
suitable as a SQL string since that would be handy for reports and it
would not be difficult to adapt the same technique to VBA code. When I
have something that works for all situations I'll post back and also
answer some of your other questions.

James A. Fortune
(e-mail address removed)

 

[BruceM]

I have become interested in a topic that was not even on my radar a week
ago, so I will continue to monitor this thread. At the least I will learn
something more about VBA.

I appreciate hearing back from you. Since I don't know what a logarithm
is or how to use one, I doubt I can fully understand the code. The carat
character also puzzles me. If I had to guess it would be that it means
"to the __ power". I realize too that I do not know what is to happen
if, for instance, the number 10.1 is entered in ten records, then those
records are added together. The numeral "10" would be displayed for each
record with my code, but the result of those records added together would
be 101 rather than 100. The more I understand just what is needed the
less I imagine my code would be of any real help. In any case it can't
account for things like 0.00024.

I've almost got it worked out. It's possible to use the SetSF function
and to create a string for display purposes only that detects and corrects
the fact that Access wants to help us by removing the tenth's place. Part
of the correction is detecting when SetSF returns an integer (such as 1,
12 or 320000). Another part of the detection is comparing something like
Len(CStr(SetSF([Input], intPlaces))) with intPlaces. That form doesn't
quite handle when the period should or should not be present. An IIf
function can display the output normally when the correction is not
needed. I'll try to set it up as something suitable as a SQL string since
that would be handy for reports and it would not be difficult to adapt the
same technique to VBA code. When I have something that works for all
situations I'll post back and also answer some of your other questions.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I have become interested in a topic that was not even on my radar a week
ago, so I will continue to monitor this thread. At the least I will learn
something more about VBA.

First I changed SetSF to be able to handle non-positive numbers:

'-----Begin 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
Dim intSign As Integer

SetSF = 0
If dblX = 0 Then Exit Function
intSign = 1
If dblX < 0 Then
dblMantissa = Log(-dblX) / Log(10#)
intSign = -1
Else
dblMantissa = Log(dblX) / Log(10#)
End If
intExponent = Int(dblMantissa)
dblMantissa = dblMantissa - intExponent
dblSP = 10 ^ dblMantissa
dblSP = Round(dblSP, intSF - 1)
SetSF = intSign * dblSP * 10 ^ intExponent
End Function

Public Function FormatSF(dblX As Double, intPlaces As Integer) As String
Dim intExponent As Integer
Dim intSign As Integer
Dim strTemp As String

If dblX <> 0 Then
If dblX < 0 Then
intExponent = Int(Log(-dblX) / Log(10) + 0.0000001)
Else
intExponent = Int(Log(dblX) / Log(10) + 0.0000001)
End If
intSign = Sgn(dblX)
If Int(dblX) = dblX And intPlaces > intExponent + 1 Then
FormatSF = CStr(dblX) & "." & String(intPlaces - intExponent - 1, "0")
Else
FormatSF = CStr(dblX)
End If
Else
strTemp = "0"
If intPlaces > 1 Then
strTemp = strTemp & "." & String(intPlaces - 1, "0")
End If
FormatSF = strTemp
End If
End Function
'-----End Module Code

Test situations:

FormatSF(SetSF(100, 1), 1) = "100"
FormatSF(SetSF(100, 2), 2) = "100"
FormatSF(SetSF(100, 3), 3) = "100"
FormatSF(SetSF(100, 4), 4) = "100.0"
FormatSF(SetSF(12, 2), 2) = "12"
FormatSF(SetSF(12, 3), 3) = "12.0"
FormatSF(SetSF(3, 4), 4) = "3.000"
FormatSF(SetSF(30, 4), 4) = "30.00"
FormatSF(SetSF(300, 4), 4) = "300.0"
FormatSF(SetSF(3000, 4), 4) = "3000"
FormatSF(SetSF(5, 1), 1) = "5"
FormatSF(SetSF(5, 2), 2) = "5.0"
FormatSF(SetSF(2120, 4), 4) = "2120"
FormatSF(SetSF(32200, 5), 5) = "32200"
FormatSF(SetSF(32222, 5), 5) = "32222"

Those were the only examples I tried. I didn't try the cases where the
input is 0. I didn't try the cases where the input is negative. I
didn't like having to use '+ 0.0000001' to keep a value whose internal
representation is barely under an integer from being chopped. Creating
a version of FormatSF using SQL looks too messy but hopefully that's not
too much of a restriction. Hopefully I'll get to your questions soon.
If your numbers are already set to the correct number of scientific
figures you shouldn't need the SetSF function, just the FormatSF
function. I'll try to explain the SetSF function in a simple way. Let
me know if you discover any situations not covered by these functions.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

I appreciate hearing back from you. Since I don't know what a logarithm is
or how to use one, I doubt I can fully understand the code. The carat
character also puzzles me. If I had to guess it would be that it means "to
the __ power". I realize too that I do not know what is to happen if, for
instance, the number 10.1 is entered in ten records, then those records are
added together. The numeral "10" would be displayed for each record with my
code, but the result of those records added together would be 101 rather
than 100. The more I understand just what is needed the less I imagine my
code would be of any real help. In any case it can't account for things
like 0.00024.

Your guess about the circumflex (^) is correct.

Fun With Logarithms

The base 10 logarithm of a number is the exponent you have to raise 10
to, in order to get the number back. If y = 10 ^ x then x is the base
10 logarithm of y, say x = log10(y). Generally speaking, the base 10
logarithm undoes the operation of raising 10 to a given power since x =
log10(10 ^ x) and x = 10 ^ (log10(x)), where x is obviously greater than
0. These relationships come directly from y = 10 ^ x and x = log10(y)
by substituting both ways. The equations you find in math texts for
dealing with things like finding the logarithm of a product come
directly from the definition of a logarithm along with the rules for
exponents. Logarithms can be defined for other bases such as e or 2.
The Log() function in VBA is based on e so a conversion formula is
required to find the base 10 logarithm.

Here's an example of one of the relationships (in case you don't have a
textbook handy):

Show Log(a ^ b) = b * Log(a)

From the context, b must be greater than 0. Let x = Log(a ^ b) and y =
b * Log(a). From the logarithm definition, e ^ x = a ^ b and a = e ^
(y/b). Substituting the 'a' into the first equation, e ^ x = e ^ (y/b)
^ b = e ^ y. Then, dividing both sides by e ^ y (which is always
positive), e ^ (x - y) = 1 implying that x = y.

Now, let's derive the conversion formula just for fun.

Let y = Log(x) base 10. Then 10 ^ y = x from the logarithm definition.
Take the natural (base e) log (call it Log). Log(10 ^ y) = Log(x).
From the result above, y * Log(10) = Log(x). So y = Log(x) / Log(10).
But y = Log(x) base 10. So Log(x) base 10 = Log(x) / Log(10).

The base 10 logarithm is useful because it breaks the range of numbers
into decade bands that behave similarly to the numbers in the other
bands except for the exponent from scientific notation.

I hope this helps,

James A. Fortune
(e-mail address removed)

 

[BruceM]

Thanks again. The logarithm stuff is stirring some vague memories, but the
synapses haven't lined up yet. I have saved your code to my own personal
help file, but have not tested it. However, I expect somebody will do a
search some day and will come upon it. I did some searching of my own, and
do not see that much has been done with significant digits, at least not
with a function that could take a wide range of input.
I really appreciate your taking the time to explain.