How can I work with fraction in access?

[Guest]

I want to write a fraction in access (ex: 11 15/16), I'm able with a format
TEXT but I'm not able to do somes calculations because the format is not
numeric. What can I do??? Can I write fraction with numeric format?

 

[Mike Labosh]

I want to write a fraction in access (ex: 11 15/16), I'm able with a format

TEXT but I'm not able to do somes calculations because the format is not
numeric. What can I do??? Can I write fraction with numeric format?

Not only can you do it, but that's what you should do. Fractional numeric
values that you intend to do math on, should ALWAYS be stored as Curency,
Single or Double.

What you are experiencing is something similar to what happens to people
that store ZIP codes in a numeric column:

1. Define a ZIPCode column as Long Integer.
2. Open the data sheet and enter a ZIP Code: 12345
3. Now enter another one in 9-digit format like this: 12345-6789
4. Now click on another record and go back and check the one from step 3.
You will find a value of 5556 because Access interprets the hyphen in the
ZIP Code as a subtraction operation, and therefore executes what it thinks
is a mathematical expression, and the value that actually gets stored to the
table is the result.

Ditto on storing phone numbers in numeric fields. Punch up your Windows
calculator and figure out what happens when you put 215-555-1212 in a
numeric column.

The bottom line: If you have a fractional value (11 15/16) that you intend
to do math on, find a way to evaluate it to an actual numeric value, and
then store the result.

 

[Allen Browne]

Some years ago, Microsoft published a database named neatcode.mdb. It
contained these functions for converting between decimal and fraction:


Function Frac2Num(X)
'
' Parses a standard fraction of the form "a/b" or "a b/c" and returns a
number.
' e.g. "2/5" or "3 1/2" are valid input.
'
Dim Temp As String, P As Integer, N As Double, Num As Double, Den As Double
If VarType(X) < 2 Or VarType(X) = 7 Then
Frac2Num = Null
ElseIf VarType(X) <> 8 Then
Frac2Num = X
Else
Temp = Trim$(X)
P = InStr(Temp, " ")
If P = 0 Then
If InStr(Temp, "/") = 0 Then
N = Val(Temp)
Else
N = 0
End If
Else
N = Val(Left$(Temp, P - 1))
Temp = Mid$(Temp, P + 1)
End If
P = InStr(Temp, "/")
If P <> 0 Then
Num = Val(Left$(Temp, P - 1))
Den = Val(Mid$(Temp, P + 1))
If Den <> 0 Then
N = N + Num / Den
End If
End If
Frac2Num = N
End If
End Function

Function Num2Frac(X)
'
' Converts a decimal number to a normalized fraction and automatically,
' determines a Denominator between 2 and 8.
'
Dim Temp As String, Fixed As Double
If (VarType(X) < 2) Or (VarType(X) > 6) Then
Num2Frac = X
Else
X = Abs(X)
Fixed = Int(X)
If Fixed > 0 Then
Temp = Str(Fixed)
End If
Select Case X - Fixed
Case Is < 0.1
If Fixed > 0 Then
Temp = Temp
Else
Temp = Str(X)
End If
Case 0.1 To 0.145
Temp = Temp + " 1/8"
Case 0.145 To 0.182
Temp = Temp + " 1/6"
Case 0.182 To 0.225
Temp = Temp + " 1/5"
Case 0.225 To 0.29
Temp = Temp + " 1/4"
Case 0.29 To 0.35
Temp = Temp + " 1/3"
Case 0.35 To 0.3875
Temp = Temp + " 3/8"
Case 0.3875 To 0.45
Temp = Temp + " 2/5"
Case 0.45 To 0.55
Temp = Temp + " 1/2"
Case 0.55 To 0.6175
Temp = Temp + " 3/5"
Case 0.6175 To 0.64
Temp = Temp + " 5/8"
Case 0.64 To 0.7
Temp = Temp + " 2/3"
Case 0.7 To 0.775
Temp = Temp + " 3/4"
Case 0.775 To 0.8375
Temp = Temp + " 4/5"
Case 0.8735 To 0.91
Temp = Temp + " 7/8"
Case Is > 0.91
Temp = Str(Int(X) + 1)
End Select
Num2Frac = Temp
End If
End Function

Function Num2FracA(X, Denominator As Long)
'
' Converts a decimal number to a fraction but doesn't normalize it.
' i.e. 3 2/4 -> 3 2/4
'
Dim Temp As String, Fixed As Double, Numerator As Long
If (VarType(X) < 2) Or (VarType(X) > 6) Then
Num2FracA = X
Exit Function
End If
X = Abs(X)
Fixed = Int(X)
Numerator = Int((X - Fixed) * Denominator + 0.5) 'Arithmatic rounding
If Numerator = Denominator Then
Fixed = Fixed + 1
Numerator = 0
End If
If Fixed > 0 Then
Temp = Str(Fixed)
End If
If Numerator > 0 Then
Temp = Temp & " " & Numerator & "/" & Denominator
End If
Num2FracA = Temp
End Function

Function Num2FracB(X, ByVal Denominator As Long)
'
' Converts a decimal number to a fraction and normalizes it.
' i.e. 3 2/4 -> 3 1/2
'
Dim Temp As String, Fixed As Double, Numerator As Long, Factor As Long
If (VarType(X) < 2) Or (VarType(X) > 6) Then
Num2FracB = X
Exit Function
End If
X = Abs(X)
Fixed = Int(X)
Numerator = Int((X - Fixed) * Denominator + 0.5) 'Arithmatic rounding
If Numerator = Denominator Then
Fixed = Fixed + 1
Numerator = 0
End If
If Fixed > 0 Then
Temp = Str(Fixed)
End If
If Numerator > 0 Then
Factor = GCF(Numerator, Denominator)
Temp = Temp & " " & Numerator / Factor & "/" & Denominator / Factor
End If
Num2FracB = Temp
End Function