Display decimal data as a fraction

[Guest]

How can I set up a database to display a decimal input as a fraction. I
would like to type in .5 and the database will show this as 1/2.

 

[Marshall Barton]

How can I set up a database to display a decimal input as a fraction. I
would like to type in .5 and the database will show this as 1/2.

That example is too simple. What should happen when the
value you type in (or more importantly calculate) is not a
nice decimal representation of a fraction.

For example, should .087 display as 1/12 or what?

 

[onedaywhen]

That example is too simple. What should happen when the
value you type in (or more importantly calculate) is not a
nice decimal representation of a fraction.

For example, should .087 display as 1/12 or what?

Excel2003 has a Fraction cell format. It renders your 0.087 as 2/23.
IMO it's not unreasonable for the OP to expect Access to have similar
number formatting capabilities.

Jamie.

--

 

[Guest]

It should be rounded as quarter (1/4 & 3/4), half and eight (1/8, 3/8 5/8 &
7/8).

 

[Marshall Barton]

Excel2003 has a Fraction cell format. It renders your 0.087 as 2/23.
IMO it's not unreasonable for the OP to expect Access to have similar
number formatting capabilities.

Sure, but is that what's really wanted? 2/23 is a rather
odd fraction if you're working in 16ths of an inch.

 

[Marshall Barton]

The function below will convert a decimal number to a
fraction (to the nearest eigth).

Displaying the fraction is a separate issue beyond using the
function. To display the fraction after the decimal number
was entered into a table, you could use the function as a
calculated field in a query. In a report, a text box can
use the function in its control source expression. If you
are using a text box on a form to enter the decimal number
abd wabt to see the fraction when you leave the text box,
you will need a second text box to display the fraction
(this arrangement can be made to behave as a single text box
by placing the fraction text box on top of the decimal text
box and switching the focus for editing.

Public Function CFractionRnd(X As Double) As String
Dim W As Variant
Dim Y As Integer
Dim N As Integer

Y = Int(X)
N = Int((X - Y) * 8 + 0.5)
If Y = 0 Then W = Null Else W = CStr(Y)
Select Case N
Case 0
CFractionRnd = W
Case 4
CFractionRnd = (W + " ") & "1/2"
Case 2, 6
CFractionRnd = (W + " ") & (N / 2) & "/4"
Case 1, 3, 5, 7
CFractionRnd = (W + " ") & N & "/8"
End Select
End Function

 

[John Nurick]

Sure, but is that what's really wanted? 2/23 is a rather
odd fraction if you're working in 16ths of an inch.

The Excel "Fraction" display format has an option for displaying 16ths -
but it's useless for ordinary purposes because it goes
1/16 2/16 3/16 4/16 ...
not
1/16 1/8 3/16 1/4 ...

 

[onedaywhen]

The Excel "Fraction" display format has an option for displaying 16ths -
but it's useless for ordinary purposes because it goes
1/16 2/16 3/16 4/16 ...
not
1/16 1/8 3/16 1/4 ...

You are describing Excel's 'As sixteenths' format. The format you seem
to want, then, is 'Up to two digits'.

Jamie.

--

 

[Tim Ferguson]

Displaying the fraction is a separate issue beyond using the
function. To display the fraction after the decimal number
was entered into a table, you could use the function as a
calculated field in a query.

Or this one...

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



B Wishes


Tim F

 

[Guest]

C

Or this one...

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



B Wishes


Tim F

 

[Marshall Barton]

Marshall Barton wrote


Or this one...

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

Very nice Tim, I am modifying my function to use something
similar ;-)

Lori,
This one deals with values near zero as well as values like
4 3/8

Public Function CFractionRnd(X As Double) As String
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

 

[Tim Ferguson]

CFractionRnd = Y & (" " + Frac(N))

I like the use of the + concatenation operator too.


All the best


Tim F

 

[John Nurick]

That won't do it either, unless you round the values to the nearest 16th
or 64th or whatever.

 

[Guest]

Is there an easy way that even I can understand how to insert this function
on my database. Please note that my programming skills are very basic & I
just copied the access database from another file. I have no idea where to
insert these calculations.

 

[Marshall Barton]

Is there an easy way that even I can understand how to insert this function
on my database. Please note that my programming skills are very basic & I
just copied the access database from another file. I have no idea where to
insert these calculations.

Select the Modules tab on the Database window, then click
the New button. This should open the Visual Basic window to
a new, blank module. Then Copy the code in my last message
and Paste it into the module. Use the Debug - Compile menu
item to make sure the code is ok. When compile does not
generate any error messages, close and save the module with
a name like basFraction.

Once that is done, you can use the CFractionRnd function
anywhere you can use any of Access' builtin functions such
as Date, Trim, etc.

 

[Guest]

Thanks so much for your help, but that went way over my head. I'll keep
searching thru the net to figure it out.

 

[Marshall Barton]

Searching the web for how to create a module will probably
set you adrift in a near infinite sea of related
information. Better for you to start with the first step I
told you to use and post back at the point where you run
into a problem. "over my head" isn't exactly a question I
can answer, so please try to be specific. Perhaps you do
not know what the Database window is or maybe you can see
the window but it isn't big enough for you to see its
content??

Don't forget that Access Help is a focused facility for
assistance (it can be difficult to locate a specific topic
though).