1. ## 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. ## 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.

3. ## 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. ## 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. ## 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.

6. ## Re: fractions (access2000)

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

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

#### Posting Permissions

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