how do I format a number as a fraction

[Guest]

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]

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.

 

[Guest]

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]

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:
FractionecimalToFrac([FieldName])

Or an unbound control source:
=DecimalToFrac([FieldName])

 

[Joseph Meehan]

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.