How do I import fractions into access database?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have developed a database that uses excell to crunch numbers then spit them
back to Access. But no matter how I format them in excell the fractional part
always comes back as a decimal in access. I've tried making formatting the
cells for text, text using =TEXT(), quotation marks, making everything on the
worksheet text..... What now? I need fractions in my report.
 
Can you write a function that converts decimals back into text fraction
display?
 
I have developed a database that uses excell to crunch numbers then spit them
back to Access. But no matter how I format them in excell the fractional part
always comes back as a decimal in access. I've tried making formatting the
cells for text, text using =TEXT(), quotation marks, making everything on the
worksheet text..... What now? I need fractions in my report.
Click to expand...

Import it as decimals, then use a functon to convert the decimal to a
fraction.

Place the following function in 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
===

Then call it from a query:
Fractions: DecimalToFrac([YourField])

Set the criteria for [YourField] to
Is Not Null
 
Back
Top