Bill said:
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