make age as a fraction

D

Database User

i have a datediff field that gives me the age of students. I need it to be
quite exact so it gives it to me in 2 decimal places. How can i change this
to be a fraction
e.g 8 1/2 years old
 
D

Duane Hookom

I just googled
vba convert decimal to fraction
and got a hit on the first link. You might need to send the function only
the decimal value (no whole numbers)

From www.freevbcode.com Author: Waty
Public Function Dec2Frac(ByVal f As Double) As String

Dim df As Double
Dim lUpperPart As Long
Dim lLowerPart As Long

lUpperPart = 1
lLowerPart = 1

df = lUpperPart / lLowerPart
While (df <> f)
If (df < f) Then
lUpperPart = lUpperPart + 1
Else
lLowerPart = lLowerPart + 1
lUpperPart = f * lLowerPart
End If
df = lUpperPart / lLowerPart
Wend
Dec2Frac = CStr(lUpperPart) & "/" & CStr(lLowerPart)
End Function
 
D

Database User

sounds good but where do i putt this code and how do i give them just the
decimal part of the number
thanks for your help
 
D

Duane Hookom

Create a new standard module in your database and copy the code into it from
Public Function
to
End Function
Save the module with the name "modConversions".
You can then use this function in queries or control source or code like you
would most built in functions.

In your query, you would use an expression like:

AgeFraction: Int(AgeDecimal) & " " & Dec2Frac(AgeDecimal - Int(AgeDecimal))

Replace AgeDecimal with your field or expression that creates your current
"age of students".
 
D

Database User

thanks for that
now how do i round it off that the fraction is either 1/4
1/2
3/4
please let me know thannks
 
D

Duane Hookom

I missed any earlier suggestion that you wanted your results rounded to 1/2
or 1/4s. I would abandon the earlier suggestion and write a function that
finds the decimal value and if it is between .125 and .375 return 1/4 etc.
 
D

Database User

I am not so familiar with writing functions how would i do that?
thanks so much
 
D

Duane Hookom

You can try an in-elegant function like:
Public Function Dec2Qtrs(ByVal dblAmt As Double) As String
Dim intWholeNumber As Long
Dim dblDecimalPart As Double
Dim strReturn As String
Dim intQuarters As Integer
intWholeNumber = Int(dblAmt)
dblDecimalPart = dblAmt - intWholeNumber
intQuarters = CInt(dblDecimalPart * 4)
Select Case intQuarters
Case 0
strReturn = intWholeNumber & ""
Case 1
strReturn = intWholeNumber & " 1/4"
Case 2
strReturn = intWholeNumber & " 1/2"
Case 3
strReturn = intWholeNumber & " 3/4"
Case 4
strReturn = intWholeNumber + 1 & ""
End Select
Dec2Qtrs = strReturn

End Function
 
Top