Results 1 to 7 of 7
Thread: fractions (access2000)

20031120, 03:01 #1
 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

20031120, 03:46 #2
 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

20031120, 16:50 #3
 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 userdefined 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 builtin 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

20031120, 22:34 #4
 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 saywell, 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!

20031121, 12:47 #5
 Join Date
 Aug 2001
 Location
 Evergreen, CO, USA
 Posts
 6,613
 Thanks
 3
 Thanked 58 Times in 58 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

20031123, 00:21 #6
 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 ?

20031123, 00:37 #7
 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