Fractions in Access, I think I know how, but.....?

[zr500racer]

I had this working by creating a macro to send table information out to a pre-formatted Excel Spreadsheet that converts the decimals to fractions. It then converts the fractions to text. I then linked this spreadsheet back into the database with the decimals converted over to fractions converted to text. This worked pretty good, but it was fairly unstable. If anyone has any ideas on how to make this work better, please respond... I think that I can export the field data to a temporary excel file,reimport the data back into a table, then "Kill" the the temp file. My resoning for doing this is because I believe that the Excel file became corrupted, therefore if the excel file is created, used, then deleted, there's less opportunity for corruption, plus one less file I will have to maintain. Will this work?

 

[jrbarnett]

How about having a table

Decimal Text
0.25 Quarter
0.50 Half
0.75 Three Quarters

etc
You can then do a lookup on the text, and put in other values against the fraction.

You could use the Number-CInt(Number) to retrieve the fractional part of a number and add the whole number to the fraction.

John

 

[zr500racer]

I used [=TEXT((B2), "# ??/??&quot] to change fractions to text in my spreadsheet. I send lots of cutsheets out to cabinet makers, they don't like decimals, so I need my Access DataBase to output fractions in my cutsheet report.... I wish that they would just learn decimals, ahhhhh, if life were so sweet...

 

[MichaelRed]

Certainly NOT the only way. The following will return a reasonable approximation of the desired. It lacks any real / useful effor chacking, and is of limited 'precision' - either of which the user is free to improve according to their needs.

Public Function basDec2Fraction(ValIn As Double) As String

    'Michael Red    5/4/2003
    'Tek-tips for thread181-566895 from zr500racer,

    Dim strDec As String
    Dim ValParts() As String
    Dim ReducedFract As String
    Dim sngVal As Single
    Dim MyFract As Single
    Dim MyMod As Single
    Dim Idx As Long

    strDec = CStr(ValIn)
    ValParts = Split(strDec, ".")

    If (UBound(ValParts) <> 1) Then
        'Uh Oh!  No Decimal to convert.
        basDec2Fraction = ValParts(0)
        Exit Function
    End If

    MyFract = Val(ValParts(1)) * 10 ^ -(Len(ValParts(1)))

    sngVal = Val(ValParts(1)) / 2
    Idx = 1
    Do While Idx <= 200
        MyMod = 1 / (Idx / 10)
        If ((Abs(1 - (MyFract) / MyMod)) < sngVal) Then
            ReducedFract = &quot;1/&quot; & CStr(Idx)
            sngVal = (Abs(1 - (MyFract) / MyMod))
        End If
        Idx = Idx + 1
    Loop

    basDec2Fraction = CStr(ValParts(0)) & &quot; and &quot; & ReducedFract

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[mymou]

Bit late - but anyway.

With a reference to the Excel object Library you could use:

Excel.WorksheetFunction.Text(yourData, "# ??/??&quot from within access.

Very clean