convert fractions to decimals

  • Thread starter Thread starter gi
  • Start date Start date
G

gi

I have a field called 'volume' that has data in fractions
(ex. 15 3/8), I would like to convert the fractions to
decimals and use it for further calculations. How do I
achieve this? Thanks.
 
I have a field called 'volume' that has data in fractions
(ex. 15 3/8), I would like to convert the fractions to
decimals and use it for further calculations. How do I
achieve this? Thanks.

All numbers must have a space between the whole number and the
fraction (as your 15 3/8 example does).
Paste the following function into a module.

Function ConvertFraction(strGetNumber As String) As Double
' Will convert a fraction, such as 12 3/4 to
' it's decimal equivalent, 12.75
Dim dblFraction As Double
Dim intPosition As Integer
Dim strTop As String
Dim strBottom As String
Dim dblWhole As Double
Dim strFraction As String
On Error GoTo Err_Convert

intPosition = InStr(strGetNumber, "/")
If intPosition = 0 Then
ConvertFraction = strGetNumber ' It's a whole number
Exit Function
End If

intPosition = InStr(strGetNumber, " ")
If intPosition > 0 Then
dblWhole = Val(Left(strGetNumber, intPosition - 1))
Else
dblWhole = 0
End If

strFraction = Mid(strGetNumber, intPosition + 1)
intPosition = InStr(strFraction, "/")
strTop = Left(strFraction, intPosition - 1)
strBottom = Mid(strFraction, intPosition + 1)
dblFraction = Val(strTop) / Val(strBottom)
ConvertFraction = dblWhole + dblFraction

Exit_Function:
Exit Function

Err_Convert:
MsgBox "Error #: " & Err.Number & " " & Err.Description,
vbInformation
Resume Exit_Function
End Function
===============

You can call it from a query or the control source of a control on a
form or report.
In a query.
ChangeToDecimal:ConvertFraction([Fieldname])

In a control's control source:
=ConvertFraction([FieldName])

15 3/8 = 15.375
 
Can you round-trip it to Excel? Excel can do it easily,
just by changing the cell format.
 
gi said:
I have a field called 'volume' that has data in fractions
(ex. 15 3/8), I would like to convert the fractions to
decimals and use it for further calculations. How do I
achieve this?


You can use an expression:

Eval(Replace(thefield, " ", "+"))
 
Back
Top