Number Format Problem

[Guest]

I'm having an issue with formating a field so that I can enter numbers with
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]

Try putting "00" in both the Format and Input Mask properties of the control
you're using. See what you get with that, and then let me know if that's
what you're looking for.

 

[Rick Wannall]

Try putting "00" in both the Format and Input Mask properties of the control
you're using. See what you get with that, and then let me know if that's
what you're looking for.

 

[Guest]

If you simply want to set the field to add 2 decimal place you can do this in
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

 

[Guest]

If you simply want to set the field to add 2 decimal place you can do this in
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

 

[Guest]

Basically this is what I need to do. I need to be able to enter numbers like
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

 

[Guest]

Basically this is what I need to do. I need to be able to enter numbers like
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]

You're not going to like this answer, if I'm correct: There's just no way
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

 

[Rick Wannall]

You're not going to like this answer, if I'm correct: There's just no way
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

 

[Guest]

There is a very simple way to do this (although it may not be what you want).

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

 

[Guest]

There is a very simple way to do this (although it may not be what you want).

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

 

[BruceM]

Something like this in a text box's After Update event should work:

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.

 

[BruceM]

Something like this in a text box's After Update event should work:

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.

 

[Guest]

Thanks for all the great tips guys. However, I noticed something interesting
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!

 

[Guest]

Thanks for all the great tips guys. However, I noticed something interesting
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]

You can do some math with text fields (such as in a totals query), and you
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.

 

[BruceM]

You can do some math with text fields (such as in a totals query), and you
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.

 

[Guest]

It is sometimes difficult for professionals who work in "non-scientific"
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.

 

[Guest]

It is sometimes difficult for professionals who work in "non-scientific"
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.

 

[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.