Converting Fractions to number - Microsoft Access Expert 26


	Back to Access Expert 26 Comments List

Converting Fractions to number

Garry Frank

3 years ago

Is there a way to convert a fraction (i.e., Text string such as "1/5") to a number equivalent (0.20) in Access? This works well in Excel, and if you have a database of recipes which normally would read "1/2" + the measurement, it is not easily read if you use the decimal equivalent, especially on a measurements that do not divide evenly into 1, such as 1/3 (0.3333...). Yet, when creating a grocery list you need to know how much you need.

	Alex Hedley @Reply 3 years ago
Can't remember if it's covered in Math in Fields.

	Adam Schwanz @Reply 3 years ago
If not, you can use InStr to find the "/" then use something like Left to split the two sets of numbers and divide them.

	Kevin Robertson @Reply 3 years ago
Are these the results you are looking for (see screenshot)?

	Kevin Robertson @Reply 3 years ago

Kevin Robertson @Reply

3 years ago

I updated the function to take into consideration values like '1 1/2', '5 2/4' and so on.
Also, Null values are also covered and will return a zero (0).

Public Function FracToDec(Fraction As Variant) As Double



    Dim FracArray() As String, SpacePos As Long, WholeNum As Long, MyFrac As String

    

    On Error GoTo ErrorHandler

    

    WholeNum = 0

    SpacePos = Nz(InStr(Fraction, " "), 0)

    

    If SpacePos > 0 Then

        WholeNum = Trim(Left(Fraction, SpacePos))

        MyFrac = Trim(Right(Fraction, Len(Fraction) - SpacePos))

    Else

        MyFrac = Nz(Fraction, 0)

    End If

    

    FracArray = Split(MyFrac, "/")



    If Not IsNull(Fraction) Then

        FracToDec = WholeNum + (Round(Val(FracArray(0)) / Val(FracArray(1)), 2))

    End If

    

    Exit Function

    

ErrorHandler:

    FracToDec = 0

    

End Function

The screenshot below shows the results.

	Kevin Robertson @Reply 3 years ago

	Garry Frank OP @Reply 3 years ago
Wow! Thanks. I appreciate the input. Yes, the code is very helpful.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 26.

Next Unseen