Fraction Format

[Lori]

I have 3 fields (size dimensions) in my database that print on a part report.
When Engineers enter data it is in decimal format but the materials dept
would like to see it in fractions on a setup request report.

I have downloaded the following "neatcode" but don't know enough about VB to
know how to use the code or where to put it.

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



Any help is much appreciated.

 

[Roger Carlson]

Where you put it:
Create a General Module in the Modules tab of the Database Window. Paste
the code there.

How to use it:
It all depends on how you plan to use it. Is each number in a separate
field? Do you display them in separate controls on a report or concatenated
into one control? How would you display the numbers in Decimal format? If
you can tell me that, I should be able to show you how to use the functions.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Lori]

Roger,

I use them as 3 separate fields in my forms with the Engineers entering them
as decimals. They were set up that way to calculate square footage, truck
loads, etc. but I want them to be converted to fractions, in their own
separate fields, on a Parts report so the Materials Dept. doesn't have to do
a conversion when typing text descriptions that include the sizes.

 

[Roger Carlson]

OK then. You could use the functions in a query which feeds the report, or
in controls on the report. I'd suggest a query. Something like this:

SELECT Field1, Field2, Num2FracB([YourTable].[Your1stDimension],16) AS
Your1stDimension,
Num2FracB([YourTable].[Your2ndDimension],16) AS Your2ndDimension,
Num2FracB([YourTable].[Your3rdDimension],16) AS Your3rdDimension
Field3, Field4 FROM YourTable

Now, of course, you will have to substitute your actual table and field
names:
Field1, Field2, etc. should be the other fields in your table
Your1stDimesion, etc. should be the actual dimension field names,
and YourTable should be substituted with your actual table name.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[fredg]

Roger,

I use them as 3 separate fields in my forms with the Engineers entering them
as decimals. They were set up that way to calculate square footage, truck
loads, etc. but I want them to be converted to fractions, in their own
separate fields, on a Parts report so the Materials Dept. doesn't have to do
a conversion when typing text descriptions that include the sizes.

Try this.
Copy the following code into a new Module.

Public Function DecimalToFrac(DecimalIn) As String

'Convert decimal 1.5 to Fraction 1 1/2

Dim strWholePart As String
Dim varNumerator As Variant
Dim lngDenominator As Long
Dim intX As Integer
strWholePart = Int(DecimalIn)
intX = InStr([DecimalIn], ".")

If intX = 0 Or IsError(Mid([DecimalIn], intX + 1)) Then
DecimalToFrac = strWholePart
Exit Function
End If

varNumerator = Mid(DecimalIn, InStr(DecimalIn, ".") + 1)
lngDenominator = 1 & String(1 * Len(varNumerator), "0")

Do While lngDenominator Mod 5 = 0 And varNumerator Mod 5 = 0
varNumerator = varNumerator / 5
lngDenominator = lngDenominator / 5
Loop

Do While lngDenominator Mod 2 = 0 And varNumerator Mod 2 = 0
varNumerator = varNumerator / 2
lngDenominator = lngDenominator / 2
Loop

DecimalToFrac = strWholePart & " " & varNumerator & "/" &
lngDenominator

End Function
=========

Name the module mdlFractions

You can call it from a query, using:
FractionecimalToFrac([DecimalfieldName])

or..
In the control source of an unbound control"\:
= DecimalToFrac([DecimalfieldName])

Results look like this:

DecimalField Fraction
12.36 12 9/25
63.125 63 1/8
100 100
3692.12 3692 3/25
1.5 1 1/2
1.25 1 1/4
10.33 10 33/100
125.485 125 97/200