Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fractions (access2000)

    other than making it a text field, is there a way to use fractions as data-- I have just learned that the testing scores that need to be used in the database need to be expressed as a fraction 12 out of twenty possible right--

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: fractions (access2000)

    Why not have a field for the number of correct answers and then simply build the fraction as a string when you need to? Just because you may need to express them in reports, etc., as fractions, doesn't mean you necessarily have to store the data that way.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: fractions (access2000)

    As noted, in Access (unlike Excel) numerical values cannot be displayed as fractions (1/2, 1/4, etc). To convert a number to fractional format for display purposes (such as in a report), you could convert the number to a string using a user-defined function. If interested, here is a simplistic example:

    Public Function NumToFraction(ByVal dblNum As Double, _
    ByRef intDenominator As Integer) As String

    ' Convert number to string formatted as fraction
    ' dblNum = number to be converted
    ' intDenominator = Denominator (number to divide by (2 = 1/2, 3 = 1/3, etc))

    Dim dblFrac As Double ' Fractional part of number
    Dim intNum As Integer ' Integer part of number
    Dim intNumerator As Integer ' Numerator (top)
    Dim fNeg As Boolean ' Flag negative values

    ' Flag negative number:
    If dblNum < 0 Then
    dblNum = Abs(dblNum)
    fNeg = True
    End If

    ' Round to nearest fractional value:
    dblNum = Round(dblNum * intDenominator, 0) / intDenominator
    ' Separate number into Integer & Fractional values:
    intNum = Int(dblNum)
    dblFrac = dblNum - intNum
    ' Determine numerator:
    intNumerator = dblFrac / (1 / intDenominator)

    If intNumerator > 0 Then
    If intNum > 0 Then
    NumToFraction = intNum & Chr$(32) & intNumerator & "/" & intDenominator
    Else
    NumToFraction = intNumerator & "/" & intDenominator
    End If
    Else
    NumToFraction = intNum
    End If

    If fNeg = True Then
    NumToFraction = "-" & NumToFraction
    End If

    End Function

    Sample results:
    ? NumToFraction(1.375,8)
    1 3/8
    ? NumToFraction(2.27,4)
    2 1/4
    ? NumToFraction(.775,100)
    78/100
    ? NumToFraction(-10.345,1000)
    -10 345/1000

    Note that function uses VBA Round function to round number to nearest fractional value based on specified denominator for the fraction (2nd argument). You can replace built-in Round function with custom rounding function if deemed necessary - for sample custom rounding functions, see MSKB Article 196652:

    HOWTO: Implement Custom Rounding Procedures

    Keep in mind the "fraction" returned by NumToFraction function is a text string and cannot be used in calculations - for calculations use original value passed to function, keeping in mind also that function will round input value to get a "whole number" for the fraction's numerator for the specified denominator. Function can be modified as necessary if you want fractions formatted some other way for negative numbers, etc.

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fractions (access2000)

    well, that is what I thought; but the people doing the data entry dont like it that way sooooo now I have to change their database again (most of my changes are due to poor information from them during the development) and they now tell me that even though they SAID that all children would have health exam date (so silly me I made it a required field) now they complain that sometimes they cant enter a health record-- I ask "what are you trying to enter" They say--well, it won't let us enter unless we have a health exam date-- soooo instead of calling me, they just started entering data in memo fields-- will make it a problem when it comes time to do reports!

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: fractions (access2000)

    Such is the life of a database developer - users never tell you the whole story. For that reason we prototype frequently, and fairly extensive requirements reviews - but it is still one of the major challenges.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fractions (access2000)

    so am I locked into text file if they want to enter 4 of 12 as 4/12 ?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: fractions (access2000)

    You're locked into storing it as text if you want to store it as 4/12. If you put the two values in separate fields, you can store them as numbers.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •