Improve user experience with fractions

[ClaytonM]

So my wife started a bakery and she was occasionally screwing up on multiplying recipes and tracking costs in orders. I opened my big mouth and said I could write a database to do all that, no problem. Then I realized cooks like to work in fractions and Access has no way to deal with fractions like Excel.:banghead:

So I wrote some code to convert fractions as strings back and forth and store as doubles, as I need to do calculations on the values. Where I ran into a roadblock is how to handle them on the recipe entry form so the user isn't entering in one text box and seeing the data in another or doing a pop-up. Not a good user experience if you cannot enter data and hit tab. Any suggestions?

 

[JHB]

Could you show some sample of input and how you would like it to show?

 

[ClaytonM]

Ok JHB, it is attached. The rough version of the form is attached. Under ingredients subform, the first column is how it is stored, which I would prefer not to display. The second is a textbox displaying it converted into fraction via this in its control: =FractionIt([Amount]) FractionIt is my conversion function

It would be nice to enter the fraction directly over the displayed fraction, like you would do in normal entry. I don't even know of that is possible. Best workaround?

Thanks!

 

[Galaxiom]

Put the fractions in a combo and choose them.

Have a look at this thread where entering fractions was discussed.

 

[ClaytonM]

Not bad. I looked at this before Galaxiom. Its a little complicated for the users as they enter one value in two places instead of just typing a fraction, like an excel cell. I will try it out on them.

 

[JHB]

You can use the Excel fraction function in MS-Access, if you set the reference to the Excel library.
Example:

  MsgBox (Excel.WorksheetFunction.Text(2.125, "### ?/?"))

 

[ClaytonM]

I tried the combo boxes on my wife and got a reply: "Why can't we just enter a fraction like Excel"

I didn't thing to pull in the excel library JHB. I already had written my code for fraction conversion. Probably would have saved me some time. Live and learn.

I just figured it out. I know it is in bad style, but i decided to store both a text type fraction(for the user to see) and a double(for calculations) in my table.

The user can enter a fraction like they do in Excel. Also like excel, the software corrects the fraction if it is improperly formatted, say 3/2 evaluates to 1 1/2. It also discards extra spaces. The user is restricted to 1/8 accuracy. I will post it here in case anybody else wants to tackle a recipe database.

In my form I have:

Private Sub Amount_AfterUpdate()
'Evaluate and fix the user entered fraction by converting it to double and back and save the fraction
    Me.Amount = FractionIt(DoubleIt([Amount]))
'Save the double
    Me.AmountD = DoubleIt([Amount])
End Sub

And here is my convert to fraction code:

Public Function FractionIt(dblNumIn As Double) As String
'FractionIt takes a double and returns a string with a proper or mixed fraction
'rounded up to the nearist 1/8
'If > 1, a mixed fraction is returned
'If < 1, a proper fraction is returned
'If integer, a whole number is returned
'On error, it kicks the error back to the user
On Error GoTo Err_FractionIt

Dim strFrac As String
Dim strWholeNum As String
Dim dblRem As Double

'pull out whole number to operate on seperatly
strWholeNum = Fix([dblNumIn])
dblRem = [dblNumIn] - [strWholeNum]
'evaluate the fraction part
Select Case dblRem
    Case 0
        strFrac = ""
    Case Is <= 0.126
        strFrac = "1/8"
    Case Is <= 0.26
        strFrac = "1/4"
    Case Is <= 0.334
        strFrac = "1/3"
    Case Is <= 0.376
        strFrac = "3/8"
    Case Is <= 0.51
        strFrac = "1/2"
    Case Is <= 0.626
        strFrac = "5/8"
    Case Is <= 0.667
        strFrac = "2/3"
    Case Is <= 0.76
        strFrac = "3/4"
    Case Is <= 0.876
        strFrac = "7/8"
    Case Is < 1
        strFrac = "1"
End Select
'Case fraction evals to 1, add whole number and 1
If strFrac = "1" Then
    FractionIt = strWholeNum + 1
'Case fraction only, not mixed
ElseIf strWholeNum = "0" Then
    FractionIt = strFrac
'Case whole number only
ElseIf strFrac = "" Then
    FractionIt = strWholeNum
'Case mixed fraction
Else
    FractionIt = strWholeNum & " " & strFrac
End If

Exit_FractionIt:
Exit Function

Err_FractionIt:
Select Case Err
Case 0

Case Else
MsgBox Err.Description
Resume Exit_FractionIt
End Select

End Function

And my fraction to double code:

Function DoubleIt(strFraction As String) As Double
'returns a double from a fraction of type mixed, proper, improper, or whole number
'Barks at the user if fraction cannot be read and assumes strange entry occoured
'Cannot handle extra spaces before, negative numbers, or other odd imput.  This causes an error or unexpected behavior
'Irrational numbers are returned with best accuracy of type double
On Error GoTo Err_strFraction
Dim intIn As Integer
'Case "-" used as seperator
intIn = InStr(1, strFraction, "-")
'If "-" not found,  look for blank space
If intIn = 0 Then
    intIn = InStr(1, strFraction, " ")
End If
'If found "-" or " "  replace it with a + "2 1/2" becomes 2+1/2
If intIn > 0 Then
    Mid(strFraction, intIn, 1) = "+"
End If
'Evaluate the above created expression, or whole number
DoubleIt = Eval(strFraction)

Exit_strFraction:
Exit Function

Err_strFraction:
Select Case Err
    Case 0

    Case Else
        MsgBox "Please enter a properly formatted fraction, like 3/4, 1 1/4, or a whole number."
        Resume Exit_strFraction
End Select
End Function

I know FractionIt could use some more validation, but since the user sees the results instantly(and I am not being paid) I don't think it is worth the trouble. Thanks for your help and time!

 

[JHB]

I didn't thing to pull in the excel library JHB. I already had written my code for fraction conversion. Probably would have saved me some time. Live and learn.

Yeah and have got a more accuracy as 1/8.
But as you wrote Live and learn, good luck!