Number Format Problem
JayIT
the desired amount of decimal places according to 2 significant digits.
The problem is, if I want to enter the number 10, it will correctly display
as 10. If I want to enter the number 2.0, it changes the display to "2".
But 2 is unacceptable because I need to show 2 significant digits. Also, I
can't just change it to a text field, because I need to do calculations on
these numbers.
I've played with the DecimalPlaces options, and the Format options, but I
can't force it to display what I want. It either adds decimals to something
like 10 so that it reads 10.0 (incorrect) or takes away the decimal in
something like 2.0.
Is there any option that will let me type in numbers in any format so that
what I see is what I get?
Thanks!
Rick Wannall
you're using. See what you get with that, and then let me know if that's
what you're looking for.
Wayne-I-M
the table (set the format to fixed and the decimal places to 2)
Is this what you are trying to do ?? or are you wanting to show 2 decimal
paces for number with less than 4 digits ? as this would not work with a
larger number (ie 234.45)
Can you explain a bit more
--
Wayne
JayIT
this in the same field EXACTLY as they appear.
For example: 12, 12.1, 2.0, 9.2, 4.0, 1.0, 0.6, 77, 15
I only need to see a decimal to the tenths spot.
Hear is the catch. A two digit number should not show a decimal IF the value
is an integer only. An example of what is incorrect: 13.0 . If it has a
value in the tenth spot, then it is OK to show it. For example: 13.1 . This
is OK. 13.0 is NOT OK. It should be seen as 13 only.
Further more, if a value is a single digit, it must ALWAYS show the tenth
spot wheter it is a zero or not. This is to satisfy a scientific rule of
showing 2 significant digits. For example: my result is 8. It must be shown
as 8.0. If my result is 8.1 it must be shown as 8.1.
Again, I need to see decimal values always and only for single digit
numbers. Otherwise, whether or not double digit numbers show a decimal value
depends on if it is a 0 or not.
Summary:
10 = GOOD
10.0 = BAD
10.1 = GOOD
1 = BAD
1.0 = GOOD
1.1 = GOOD
Thanks so much,
Jay
Rick Wannall
to do this with a numeric field in Access. The only way I can imagine
accomplishing this is to manipulate the values as string somewhere along the
way. You'd have to write a function that would recieve the string and do
the formatting.
The only good news is that you could use a numeric field to STORE the
values, but then for display use the return from the formatting function to
show the values as you want.
The code below worked well for integer inputs. Build on this to finish up
for non-integer:
Public Function FormatSignificantDigits(ByVal NbrOfSigDig As Long, ByVal
MyValue As Variant) As String
Dim sIn As String
Dim sOut As String
Dim l As Long
FormatSignificantDigits = vbNullString
If IsNull(MyValue) = True Then
Exit Function
End If
If IsNumeric(MyValue) = False Then
Exit Function
End If
sIn = CStr(MyValue)
If InStr(1, sIn, ".") = 0 Then
'Value is an integer
If Len(sIn) < NbrOfSigDig Then
sOut = sIn & "."
Do Until (Len(sOut) = (NbrOfSigDig + 1))
sOut = sOut & "0"
Loop
Else
sOut = sIn
End If
Else
'Value is not an integer.
'more code here to finish formatting
End If
FormatSignificantDigits = sOut
End Function
Wayne-I-M
Enter the "number" into a text field, ie 10, .02, 45.23, 3, 7, 8.9, etc, etc
Create a query (or use the source query) if using a form.
I have called your "number field" [TextNumber]
In a new field click build and in the box simply write =[TextNumber]
Format this new field as "Genral Number"
You will ses that the your new field is a number and can be used in
formulas, sorted, etc, etc
If entering the "Text /Number" on a form you could use AfterUpdate SetValue
to either change your entry or simply store the integra
Hope this helps
--
Wayne
BruceM
If Me.NumberField < 10 Or Me.NumberField >= 10 And Mid(Me.txtNumberField,
3) <> "" Then
Me.txtNumberField.Format = "0.0"
Else
Me.txtNumberField.Format = "0"
End If
NumberField is your number field, and txtNumberField is the text box bound
to that field. The field size (table design view) was set to Double in my
test. Decimal places are left at the default of "Auto" in both table and
text box. Note that this works only on numbers below 100. For numbers
>=100 you would need another test for that condition.
You would need the same code in the form's Current event. You could also
make this a public sub, and call it from the text box After Update event and
the form's Current event.
Another approach that may work in some cases is to use a text field, and use
the Val function to perform math. See Help for more on Val.
"JayIT" <Ja...@discussions.microsoft.com> wrote in message
news:92DAA0F1-421A-469E...@microsoft.com...
JayIT
when trying out some of these ideas. If I change my data field from Number
to Text, of course I can enter whatever I want. What's interesting is that
when I created a query to test some basic math, I was able to add these
numbers together, and sort them as if they were actual numbers and not
strings. I really don't know how this is possible, but it seems to work well
enough to use.
I'll keep all of the code in my notes in case I need to look into this
further. Thanks again!
BruceM
can sort by them, but you could end up concatenating them when you mean to
add them (fields in the same record, for instance). I don't see why you
want to force users to follow the "significant digit" convention (adding .0
if the number is below 10, but leaving it out if it is 10 or above, etc.)
when all they need to do is enter the number, but that's up to you.
"JayIT" <Ja...@discussions.microsoft.com> wrote in message
news:2229F104-7143-4207...@microsoft.com...
Larry G.
fields to understand the importance of siginifcant digits. Eventhough to you
a single decimal place seems unimportant to those of us who work in these
fields have a difficult time trying to get Access to preform in this way. I
asked this question several months ago and told my project manager that if we
are going to use Access to track analytical data we need to learn to operate
within the parameters of the program's formatting convention for numbers.
If anyone knows how to send suggestions to the Office development team I
would love to send them this suggestion.
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!
BruceM
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.
"Larry G." <Lar...@discussions.microsoft.com> wrote in message
news:7F916F46-6A41-4767...@microsoft.com...
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
MPAP...@FortuneJames.com
BruceM
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" <jimfo...@compumarc.com> wrote in message
news:uqaGIEEg...@TK2MSFTNGP02.phx.gbl...
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
MPAP...@FortuneJames.com
BruceM
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" <jimfo...@compumarc.com> wrote in message
news:eTww%23TSgG...@TK2MSFTNGP05.phx.gbl...
James A. Fortune
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
MPAP...@FortuneJames.com
BruceM
ago, so I will continue to monitor this thread. At the least I will learn
something more about VBA.
"James A. Fortune" <jimfo...@compumarc.com> wrote in message
news:OeQdK3Mh...@TK2MSFTNGP04.phx.gbl...
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
MPAP...@FortuneJames.com
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
MPAP...@FortuneJames.com
BruceM
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.
"James A. Fortune" <jimfo...@compumarc.com> wrote in message
news:eJuokAZ...@TK2MSFTNGP02.phx.gbl...