how do I format a number as a fraction

Lynlongley

unread,

Jan 11, 2005, 6:53:02 PM1/11/05

to

I have a table with valve sizes entered as numbers (e.g 1.375). How do I
convert that number to one with a fraction (1-3/8)?

Joseph Meehan

unread,

Jan 11, 2005, 7:58:40 PM1/11/05

to

Lynlongley wrote:
> I have a table with valve sizes entered as numbers (e.g 1.375). How
> do I convert that number to one with a fraction (1-3/8)?

How are you going to display the results for 1.378? 1 2/189

If you want the results and are willing to write the VBA code to fine
the LCD, it can be done.

--
Joseph Meehan

26 + 6 = 1 It's Irish Math

Lynlongley

unread,

Jan 11, 2005, 8:19:02 PM1/11/05

to

I don't care to calculate the LCD - too much other code will be required to
get it into a standard size (see below)

The valve size is 1.375 (one point three seven five) - the formats will be
in standard sizes (halves, fourths, or eighths). There will not be any
non-standard sizes (e.g. 1-2/189)

What I want displayed in the query and report is 1-3/8 (one and three eigths)

ps- Where did you get "1.378" from?

fredg

unread,

Jan 11, 2005, 10:27:59 PM1/11/05

to

On Tue, 11 Jan 2005 15:53:02 -0800, Lynlongley wrote:

> I have a table with valve sizes entered as numbers (e.g 1.375). How do I
> convert that number to one with a fraction (1-3/8)?

Copy and Paste the following function into a Module:

Public Function DecimalToFrac(DecimalIn) As String
'Convert decimal to Fraction

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
==========
You can call it from a query:
Fraction:DecimalToFrac([FieldName])

Or an unbound control source:
=DecimalToFrac([FieldName])
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Joseph Meehan

unread,

Jan 12, 2005, 6:43:10 AM1/12/05

to

Lynlongley wrote:
> I don't care to calculate the LCD - too much other code will be
> required to get it into a standard size (see below)
>
> The valve size is 1.375 (one point three seven five) - the formats
> will be in standard sizes (halves, fourths, or eighths). There will
> not be any non-standard sizes (e.g. 1-2/189)
>
> What I want displayed in the query and report is 1-3/8 (one and three
> eigths)
>
> ps- Where did you get "1.378" from?

Just a number, I did not bother to figure out what 0.378 was so I just
made a quick guess.

In that case you should be able to convert it using an iif statement in
a query or on a form or report. (note the result will be text not a number
so you will not be able to use it in any computation.