Entering "Fractions" in Access 1

[garyde]

Although Access is going to process any fractions I enter as decimals, I want to avoid a visual conversion. I'd like the fractions to look like fractions so 1/2 stays 1/2 and doesn't become .5. I'd like the answers to whatever process is subsequently applied to appear as fractions. Is that possible?

 

[PaulF]

you probably need to write a function and use Text instead of Numeric values. This example is very generic, what you'd do is use two Unbound textboxes on the Report of Form, one for the integer and one for the fraction. Make the integer textbox Right Justified, and the fraction textbox Left Justified. Then split the number using the InStr(), Left & Mid Functions and the User Defined Function below. txtData is the textbox with the complete number, txtInt is the textbox which will display the integer, and txtDec will display the fraction.


Dim intAt As Integer
If Len(txtData) > 0 Then
intAt = InStr(txtData, ".&quot
If intAt > 0 And Len(txtData) > intAt Then
txtInt = Left(txtData, intAt - 1)
txtFrac = DecimalToFraction(Mid(txtData, intAt + 1))
Else
IIf Right(txtData, 1) = ".", Left(txtData, Len(txtData) - 1), txtData
txtInt = IIf(Right(txtData, 1) = ".", Left(txtData, Len(txtData) - 1), txtData)
txtFrac = " "
End If
End If


Function DecimalToFraction(varItem As Variant)
Dim intDec As Integer
intDec = Left(varItem, 1)
Select Case intDec
Case 1
DecimalToFraction = "1/8"
Case 2
DecimalToFraction = "1/4"
Case 3
DecimalToFraction = "1/3"
Case 4
DecimalToFraction = "2/5"
Case 5
DecimalToFraction = "1/2"
Case 6
DecimalToFraction = "2/3"
Case 7
DecimalToFraction = "3/4"
Case 8
DecimalToFraction = "4/5"
Case 9
DecimalToFraction = "9/10"
Case 0
DecimalToFraction = vbNullString
End Select
End Function

PaulF

 

[garyde]

It certainly sounds like it will work. Thanks

 

[MichaelRed]

Hmmmmmmmmmmmmmmmm,

The overall process appears to capable of returning only the (9) fractions shown in the select case statement. I would find this un-acceptable for a general purpose soloution. further, the solution only checks the first digit of the decimal value , so ALL values between .1 and .2 are returned as &quot;1/8&quot;, those betwee .2 and .3 are &quot;1/4&quot;. while the example use only a simple fraction for its example, I would suspect the process would need to consider some more complex instances. Further, ALL decimals < 0.1 are returned as &quot;Null&quot;

&quot;Just for fun&quot; I attempted a different approach, but have limited the range or the numerator denominator. It does NOT alwary return the EXACT fraction, and will always return a reduced version if the error is sufficiently small (e.g. 2/4 will reduce to 1/2). Still, it offers a methodology whih can be modified to return most common 'fractions' in a reasonable form.

For particularly picly people, changing the values of the constants may improve the results. In particular, fractions which are less than the error will always return the null string, while some users may want to return some arbitrary (1 / maxdenom ?) fraction.

Public Function basDec2Frac(varDec As Variant) As String

    'To return as string which representsthe fraction
    'approximatly equal to the decimal value of the input

    Const MaxNum = 100
    Const MaxDenom = 1000
    Const MaxErr = 0.0001

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim BlnFound As Boolean

    For Jdx = 1 To MaxNum 
        For Idx = 1 To MaxDenom 
            strRtn = Jdx / Idx
            If (Abs(strRtn - varDec) <= MaxErr) Then
                BlnFound = True
                Exit For
            End If
        Next Idx
        If (BlnFound = True) Then
            Exit For
        End If
    Next Jdx

    basDec2Frac = Trim(str(Jdx)) & &quot;/&quot; & Trim(str(Idx))

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[BoxHead]

garyde,

How do you enter fractions for calculations? I tried a couple and get a &quot;type mismatch&quot; error.

When my son was beginning to work with fractions in school, I had put together a form that displayed an equation with fractions, gave him a series of command buttons to select from, and then calculated the fractions and told him if he was right.

Since he was just learning the basics, I was able to put together a table that contained all of the equations that would be presented along with the correct answers.

My daughter's going to start fractions next year and I don't have the same computer so I'd really like to know.

Thanks,

John

 

[garyde]

John:
I guess the question I'm asking is how do you enter it as a fraction and keep it like that. In my work I occasionally have to supervise some people in a telephone project (not telemarketing, don't hate me).
I need to know how long each person worked and the level of production. Time invariably throws off the data entry people. If they see a person started at 2 p.m. and finished at 5:15 p.m., they understand he was there 3 1/4 hours.
More often than not, however, what is entered in the field showing how long they worked is 3:15 for three hours and 15 minutes, not 3.25. That's a simplification but it illustrates why I want to be able to enter fractions.

 

[BoxHead]

Garyde,

Since you're working with minutes, it would seem that Paul's suggestion would work the best for you. You'll have a limited number of fractions. You should consider where you want to round off. Wanting to make it more familiar for the users, you should probably acknowledge that true fractions like 7/30th or 9/20th aren't easily recognized as 14 and 27 minutes.

Some other options to consider:

1) Using one field to enter hours and another for minutes.

2) Using &quot;3 Hours 15 Minutes&quot; for display purposes. Input mask: &quot;00 Hours, 00 Minutes&quot;

3) Making all fractions the actual minutes over a denominator of 60. Use &quot;00:00/ 6\0&quot; as the input mask for the date/time field.



HTH

John

 

[BoxHead]

Gary,

I was just curious what you had worked out.

John

 

[scking]

You asked how to enter as a fraction and see it as a fraction. Well you can't. Access will store is as a decimal value no matter what fraction you enter. Yes, all of us Access pros like to feel like we can solve every problem but Access doesn't support it. You would need to write code the convert a text string that you see in the control to a decimal and store it in the table and then write something to convert it from a decimal back to a fraction. And if I were a betting man, I would say that was not a simple say of accomplishing a relatively frivolous problem. Remember, any solution should work for all fractions and be fully tested to ensure it works. Maybe MichaelRed could solve this one.

Steve King


Steve King Growth follows a healthy professional curiosity

 

[garyde]

Paul:
I'm going to try all the suggestions and see which works best. I'm in the unusual position of having time and in the pleasant position of needing only a practical solution.
As Steve said, any solution should work in all cases -- but that's only if it is going to be applied in all cases.
I'm fortunate to be working with minutes and, with luck, I'll be able to convince my bosses that working only in 15 minute segments will be accurate enough for their needs.
As this expands, the requirements will change and I may not be able to use the same method I'll embrace now.
I'll keep everyone informed. Thanks for all your help.

 

[BoxHead]

CAN'T ?!?!?

They told Michael Jordan, &quot;You CAN'T play baseball.&quot;

They told Bob Dole, &quot;You CAN'T be President.&quot;

Well... OK, not the best examples, but look at the endorsements they get!

The fact is, Steve, it is frivolous. Fractions are simple. But you see, that's why we use them to begin teaching our kids basic math. And, there will be times when our apps are used by people who have not gone very far beyond basic math.

I think it's very unlikely that anyone needing to use fractions will require pin-point accuracy to the nth decimal place.

They say that the guy who started Dial-A-Mattress was on the verge of bankruptcy in the early days because no one seemed willing to dial 1-800-MATTRESS to buy a bed. Before he gave in to the people who said, &quot;You CAN'T sell beds over the phone&quot;, he leased more phone numbers:
1-800-MATRESS, 1-800-MATTRIS,
and every other mis-spelling of Mattress he could think of. The phones haven't stopped ringing.

I think it's all about knowing your audience. So.... for those of us who may have an audience that demands fractions, (like my six year old daughter) here's a pretty painless way to make the substitutions.

1.) Build a new table with two fields.
table name: tblFractions
field1: intDecimal (Double)
field2: strFraction (Text)

2.) Open Excel and type the following into the respective cells:
A1 1
A2 2
B1 1000
C1 = A1/B1
D1 = LCM(B1,A1)/1000
E1 = A1/D1
F1 = B1/E1
G1 = D1&&quot;/&quot;&F1
Select A1 & A2 and Drag Down to Row 999. Fill or Drag the other cells down to Row 999. {Note: if you get #Name# error on the LCM function, check help forinstructions on selecting the correct add-in.}

Column A will have the numbers 1 - 999.
Column B 1000 in all cells
Column C will have values .001 through .999
Column D has the Lowest Common Multiplier of A & B (divided by 1000 to determine the fraction's Numerator)
Column E identifies the divisor to achieve the lowest common denominator.
Column F gives you the lowest common denominator, and
Column G creates the fraction string.

Column C contains decimal values and Column G contains their fractional equivalents. Copy these two Columns and Paste Special > Values into Columns A and B on another worksheet. Select the populated cells (A1:B999) and Copy to Clipboard.

3.) Return to Access and paste the data into tblFractions.

4.) Using a form with Text4, Text10, and Command1, paste the following code into the Click event for Command1:

Private Sub Command1_Click()
Dim frmFraction As Variant, frmDecimal As Double

frmDecimal = Mid(Text4, (InStr(Text4, ".")), 4)
frmFraction = DLookup "[strFraction]", "tblFractions","[intDecimal] LIKE " & frmDecimal)

Text10 = Left(Text4, (InStr(Text4, ".") - 1)) & "  " & frmFraction

End Sub

5.) Type a number (eg: '435.605') in Text4 and Click Command1. Text10 displays 435 121/200.


John


Use what you have,
Learn what you can,
Create what you need.

 

[mymou]

Hi all

Haven't read all this carefully - but I would have thought this would be easy.

Textbox with fraction (as text)

Split this into Denominator and Numerator and store these values separately.

Stew

 

[ErikZ]

This whole thread should be a FAQ