Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Number Format Problem

Discussion in 'Information Technology' started by JayIT, Jul 28, 2006.

  1. JayIT

    JayIT
    Expand Collapse
    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!
     
  2. Loading...

    Similar Threads Forum Date
    Shabad Numbering System New to Sikhism Dec 1, 2015
    USA A Heart Attack And The Numbers Breaking News Aug 2, 2014
    Pacific Borneo: Sikhs small in number, strong in presence Breaking News Nov 11, 2013
    World Shrinking Numbers and Growing Persecution Threaten Sikhs and Hindus in Afghanistan Breaking News Nov 7, 2013
    SciTech Mammals pick offspring’s sex to maximize number of grandchildren, study shows Breaking News Jul 11, 2013

  3. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    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.
     
  4. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    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

    --
    Wayne




    "JayIT" wrote:

    > 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!
     
  5. JayIT

    JayIT
    Expand Collapse
    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


    "Wayne-I-M" wrote:

    > 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
    >
    > --
    > Wayne
    >
    >
    >
    >
    > "JayIT" wrote:
    >
    > > 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!
     
  6. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    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
     
  7. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    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


    --
    Wayne




    "JayIT" wrote:

    > 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
    >
    >
    > "Wayne-I-M" wrote:
    >
    > > 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
    > >
    > > --
    > > Wayne
    > >
    > >
    > >
    > >
    > > "JayIT" wrote:
    > >
    > > > 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!
     
  8. BruceM

    BruceM
    Expand Collapse
    Guest

    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.

    "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    news:92DAA0F1-421A-469E-85AC-A6D503F48D13@microsoft.com...
    > 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
    >
    >
    > "Wayne-I-M" wrote:
    >
    >> 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
    >>
    >> --
    >> Wayne
    >>
    >>
    >>
    >>
    >> "JayIT" wrote:
    >>
    >> > 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!
     
  9. JayIT

    JayIT
    Expand Collapse
    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" wrote:

    > 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.
    >
    > "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    > news:92DAA0F1-421A-469E-85AC-A6D503F48D13@microsoft.com...
    > > 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
    > >
    > >
    > > "Wayne-I-M" wrote:
    > >
    > >> 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
    > >>
    > >> --
    > >> Wayne
    > >>
    > >>
    > >>
    > >>
    > >> "JayIT" wrote:
    > >>
    > >> > 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!

    >
    >
    >
     
  10. BruceM

    BruceM
    Expand Collapse
    Guest

    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.

    "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    news:2229F104-7143-4207-A159-6BF05C72ECD4@microsoft.com...
    > 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" wrote:
    >
    >> 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.
    >>
    >> "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    >> news:92DAA0F1-421A-469E-85AC-A6D503F48D13@microsoft.com...
    >> > 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
    >> >
    >> >
    >> > "Wayne-I-M" wrote:
    >> >
    >> >> 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
    >> >>
    >> >> --
    >> >> Wayne
    >> >>
    >> >>
    >> >>
    >> >>
    >> >> "JayIT" wrote:
    >> >>
    >> >> > 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!

    >>
    >>
    >>
     
  11. Larry G.

    Larry G.
    Expand Collapse
    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.
    --
    Never give up, the answer IS out there, it just takes a while to find it
    sometimes!


    "BruceM" wrote:

    > 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.
    >
    > "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    > news:2229F104-7143-4207-A159-6BF05C72ECD4@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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.
    > >>
    > >> "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    > >> news:92DAA0F1-421A-469E-85AC-A6D503F48D13@microsoft.com...
    > >> > 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
    > >> >
    > >> >
    > >> > "Wayne-I-M" wrote:
    > >> >
    > >> >> 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
    > >> >>
    > >> >> --
    > >> >> Wayne
    > >> >>
    > >> >>
    > >> >>
    > >> >>
    > >> >> "JayIT" wrote:
    > >> >>
    > >> >> > 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!
    > >>
    > >>
    > >>

    >
    >
    >
     
  12. BruceM

    BruceM
    Expand Collapse
    Guest

    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.

    "Larry G." <LarryG@discussions.microsoft.com> wrote in message
    news:7F916F46-6A41-4767-AB3B-1E8E191DB42C@microsoft.com...
    > 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.
    > --
    > Never give up, the answer IS out there, it just takes a while to find it
    > sometimes!
    >
    >
    > "BruceM" wrote:
    >
    >> 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.
    >>
    >> "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    >> news:2229F104-7143-4207-A159-6BF05C72ECD4@microsoft.com...
    >> > 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" wrote:
    >> >
    >> >> 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.
    >> >>
    >> >> "JayIT" <JayIT@discussions.microsoft.com> wrote in message
    >> >> news:92DAA0F1-421A-469E-85AC-A6D503F48D13@microsoft.com...
    >> >> > 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
    >> >> >
    >> >> >
    >> >> > "Wayne-I-M" wrote:
    >> >> >
    >> >> >> 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
    >> >> >>
    >> >> >> --
    >> >> >> Wayne
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >>
    >> >> >> "JayIT" wrote:
    >> >> >>
    >> >> >> > 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!
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  13. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    BruceM wrote:
    > 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
    MPAPoster@FortuneJames.com
     
  14. BruceM

    BruceM
    Expand Collapse
    Guest

    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" <jimfortune@compumarc.com> wrote in message
    news:uqaGIEEgGHA.1204@TK2MSFTNGP02.phx.gbl...
    > BruceM wrote:
    >> 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
    > MPAPoster@FortuneJames.com
     
  15. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    BruceM wrote:
    > 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
    MPAPoster@FortuneJames.com
     
  16. BruceM

    BruceM
    Expand Collapse
    Guest

    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" <jimfortune@compumarc.com> wrote in message
    news:eTww%23TSgGHA.4776@TK2MSFTNGP05.phx.gbl...
    > BruceM wrote:
    >> 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
    > MPAPoster@FortuneJames.com
     
  17. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    BruceM wrote:
    > 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" <jimfortune@compumarc.com> wrote in message
    > news:eTww%23TSgGHA.4776@TK2MSFTNGP05.phx.gbl...
    >
    >>BruceM wrote:
    >>
    >>>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
    >>MPAPoster@FortuneJames.com



    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
    MPAPoster@FortuneJames.com
     
  18. BruceM

    BruceM
    Expand Collapse
    Guest

    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.

    "James A. Fortune" <jimfortune@compumarc.com> wrote in message
    news:OeQdK3MhGHA.3916@TK2MSFTNGP04.phx.gbl...
    > BruceM wrote:
    >> 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" <jimfortune@compumarc.com> wrote in message
    >> news:eTww%23TSgGHA.4776@TK2MSFTNGP05.phx.gbl...
    >>
    >>>BruceM wrote:
    >>>
    >>>>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
    >>>MPAPoster@FortuneJames.com

    >
    >
    > 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
    > MPAPoster@FortuneJames.com
     
  19. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    BruceM wrote:
    > 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
    MPAPoster@FortuneJames.com
     
  20. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    BruceM wrote:
    > 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
    MPAPoster@FortuneJames.com
     
  21. BruceM

    BruceM
    Expand Collapse
    Guest

    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.

    "James A. Fortune" <jimfortune@compumarc.com> wrote in message
    news:eJuokAZhGHA.836@TK2MSFTNGP02.phx.gbl...
    > BruceM wrote:
    >> 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
    > MPAPoster@FortuneJames.com
     

Share This Page