how do I work with fractions in Access?

[Guest]

What is the best way to manipulate fractions in Access. Construction industry
uses fractional measurements. These must be manipulated mathematically to
determine square footage, etc. In my industry, Glass, we must take the size
of the glass/mirror, thus: 47 7/8 x 39 15/16, and convert that to square
footage ((47 7/8 x 39 15/16)/144) in order to charge by the square foot. How
do you set up fields to accept, display, and print fractions?

 

[Douglas J. Steele]

There's nothing built into Access to allow you to do this, but you should be
able to write your own functions to convert between the two formats.

Assuming you're going to have numbers in the format x n/d, you can use the
Left function, in conjunction with InStr function to find the space, to
determine what x is, and what the fraction is. You can use something like
the following:

Function ConvertFractions(InputValue As String) As Single
Dim intSpace As Integer
Dim sngFraction As Single
Dim strFraction As String
Dim strNumeral As String

intSpace = InStr(InputValue, " ")
If intSpace > 0 Then
strNumeral = Left$(InputValue, intSpace - 1)
strFraction = Mid$(InputValue, intSpace + 1)
sngFraction = Eval(strFraction)
ConvertFractions = CSng(strNumeral) + sngFraction
Else
ConvertFractions = Eval(InputValue)
End If

End Function

Here's the result of using that function in the Immediate window:

?ConvertFractions("47 7/8")
47.875
?ConvertFractions("39 15/16")
39.9375
?ConvertFractions("3/4")
0.75

Converting back to fractions from decimal isn't quite as easy...

 

[Arvin Meyer [MVP]]

What is the best way to manipulate fractions in Access. Construction industry
uses fractional measurements. These must be manipulated mathematically to
determine square footage, etc. In my industry, Glass, we must take the size
of the glass/mirror, thus: 47 7/8 x 39 15/16, and convert that to square
footage ((47 7/8 x 39 15/16)/144) in order to charge by the square foot. How
do you set up fields to accept, display, and print fractions?

A computer really only recognizes true numbers so 39 7/8 is really text. But
I wrote this some time ago to deal with building fractions on sets of plans
that I had to deal with. These will convert either way, although Doug's
function may be more elegant than my FracToNum() function. The FractionIt()
function rounds down to 64ths


Public Function FractionIt(dblNumIn As Double) As String
'====================================================================
' Name: FractionIt
' Purpose: Converts a double into a string representing a rounded fraction
' Inputs: dblNumIn As Double
' Returns: String
' Author: Arvin Meyer
' Date: December 12, 1998
' Comment: Rounds down from 1/64 over
'====================================================================
On Error GoTo Err_Handler

Dim strFrac As String
Dim strSign As String
Dim strWholeNum As String
Dim dblRem As Double

If dblNumIn < 0 Then
strSign = "-"
dblNumIn = dblNumIn * -1
Else
strSign = " "
End If

strWholeNum = Fix([dblNumIn])

dblRem = [dblNumIn] - [strWholeNum]

Select Case dblRem
Case 0
strFrac = ""
Case Is < 0.046875
strFrac = "1/32"
Case Is < 0.078125
strFrac = "1/16"
Case Is < 0.109375
strFrac = "3/32"
Case Is < 0.140625
strFrac = "1/8"
Case Is < 0.171875
strFrac = "5/32"
Case Is < 0.203125
strFrac = "3/16"
Case Is < 0.234375
strFrac = "7/32"
Case Is < 0.265625
strFrac = "1/4"
Case Is < 0.296875
strFrac = "9/32"
Case Is < 0.328125
strFrac = "5/16"
Case Is < 0.359375
strFrac = "11/32"
Case Is < 0.390625
strFrac = "3/8"
Case Is < 0.421875
strFrac = "13/32"
Case Is < 0.453125
strFrac = "7/16"
Case Is < 0.484375
strFrac = "15/32"
Case Is < 0.515625
strFrac = "1/2"
Case Is < 0.546875
strFrac = "17/32"
Case Is < 0.578125
strFrac = "9/16"
Case Is < 0.609375
strFrac = "19/32"
Case Is < 0.640625
strFrac = "5/8"
Case Is < 0.671875
strFrac = "21/32"
Case Is < 0.703125
strFrac = "11/16"
Case Is < 0.734375
strFrac = "23/32"
Case Is < 0.765625
strFrac = "3/4"
Case Is < 0.796875
strFrac = "25/32"
Case Is < 0.828125
strFrac = "13/16"
Case Is < 0.859375
strFrac = "27/32"
Case Is < 0.890625
strFrac = "7/8"
Case Is < 0.921875
strFrac = "29/32"
Case Is < 0.953125
strFrac = "15/16"
Case Is < 0.984375
strFrac = "31/32"
Case Is < 1
strFrac = "1"
End Select

If strFrac = "1" Then
FractionIt = strSign & (strWholeNum + 1)
Else
FractionIt = strSign & strWholeNum & " " & strFrac
End If

Exit_Here:
Exit Function

Err_Handler:
Select Case Err
Case 0

Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Select

End Function

Function FracToNum(strNum As String) As Double
'====================================================================
' Name: FracToNum
' Purpose: Converts a string into a Double
' Inputs: strNum As String
' Returns: Double
' Author: Arvin Meyer
' Date: Date: December 12, 1998
' Updated: November 30, 1999
'====================================================================
On Error GoTo Err_Handler

Dim strToTrim As String
Dim intGetSpace As Integer

strToTrim = Trim$(strNum)

If Len(strToTrim) = 0 Then
FracToNum = 0
Exit Function
End If

intGetSpace = InStr(strToTrim, " ")

If intGetSpace = 0 Then
FracToNum = Eval(strToTrim)
Exit Function
Else
FracToNum = Eval(Left$(strToTrim, intGetSpace - 1) & _
" + " & Right$(strToTrim, Len(strToTrim) - intGetSpace))
End If

If Left(strToTrim, 1) = "-" Then
FracToNum = FracToNum * -1
End If

Exit_Here:
Exit Function

Err_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access