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