(REPORTS) Display decimal data as a fraction

[Guest]

Is there a simple way to display dimension on a report in fractions? The
database input is in decimals but I want the report print to be with
fractions. The report display should be rounded as quarter (1/4 & 3/4),
halves or eight (1/8, 3/8 5/8 & 7/8).

 

[Allen Browne]

Lori, Microsoft released a NeatCode sample database that included a module
for converting fractions to/from decimal:
http://support.microsoft.com/?id=177972

It's A97, so you will probably need to convert to your version.

 

[Tim Ferguson]

Is there a simple way to display dimension on a report in fractions?
The database input is in decimals but I want the report print to be
with fractions. The report display should be rounded as quarter (1/4
& 3/4), halves or eight (1/8, 3/8 5/8 & 7/8).

Public Function ToSixteenths(Fraction As Double) As String

Dim n As Integer
Dim resultArray As Variant

resultArray = Array("0", _
"1/16", "1/8", "3/16", "1/4", _
"5/16", "3/8", "7/16", "1/2", _
"9/16", "5/8", "11/16", "3/4", _
"13/16", "7/8", "15/16", "1")

n = Int(Fraction * 32)
If n > 0 Then n = (n - 1) \ 2 + 1
ToSixteenths = resultArray(n)

End Function

Public Sub Test16ths()
Dim d As Double
For d = 0 To 1 Step 0.01
Debug.Print d; Tab(8); ToSixteenths(d)

Next d

End Sub


This came up very recently, within the last week or so. Can't find the
thread itself. This is what I posted and Marshall (I think) extended it
into a fully working function -- try googling on this group.

Best wishes


Tim F

 

[Guest]

Can you tell me what I need to do to add the function to my database. Please
tell me where to click step by step (I'm not a programmer).

 

[Tim Ferguson]

Can you tell me what I need to do to add the function to my database.
Please tell me where to click step by step (I'm not a programmer).

First, find the Visual Basic editor by choosing Tools > Macros > Visual
Basic Editor;

Then create a new module to hold your function in, by choosing Insert >
Module

Then copy and paste the function definition given in the previous post[1]
from the Public Function line to the End Function line inclusive.

Choose Debug > Compile to make sure that the copy and past worked
properly.

Now you can use the function on your forms by typing something like this
into the ControlSource of the textbox (of course, you replace MyField
with whatever the name is of the field you want displayed).

= ToSixteenths([MyField])

[1] I've found Marsh's original complete function: it's here and I have
put in a wrapper so that it doesn't fall over with Nulls and other
illegal input:


' **** copy everything from here ****

Public Function CFractionRnd(X As Double) As String
' Marsh
' MVP [MS Access]

Dim Frac As Variant 'improved with Tim Ferguson ideas
Dim Y As Integer
Dim N As Integer
Frac = Array(Null, "1/8", "1/4", "3/8", "1/2", "5/8", "3/4", "7/8")
Y = Int(X + 1 / 16)
N = Int((X + 1 / 16 - Y) * 16) \ 2
If Y = 0 Then
CFractionRnd = IIf(N, Frac(N), "0")
Else
CFractionRnd = Y & (" " + Frac(N))
End If
End Function


Public Function CFRWrap(AValue As Variant) As Variant
If IsNull(AValue) Then
CFRWrap = Null

ElseIf Not IsNumeric(AValue) Then
CFRWrap = Null

Else
CFRWrap = CFractionRnd(CDbl(AValue))

End If
End Function

' **** copy everything down to here ****


and the function you put into the TextBox.Controlsource is
=CFRWrap([MyField])

Hope that helps


Tim F