I want to use fractions in Access form

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

Guest

I am using fonr MS Sans Serif .. I need to use things like 2 and half pence,
I want to use fractions instead of decimal.
 
Sorry, you can't in any meaningful way.

Some fonts may allow you to insert one quarter, one half or three quarters
using Alt-0188, Alt-0189 or Alt-0190, but that will strictly be text, not
numbers that you can do calculations on.
 
Gooner,

Store the value as text, with a consistent delimiter between the whole and
fraction portions, such as:

2-1/2
3-1/4
15-1/8

Then you can use a public function to regain its value, and call this
function in a calculated control on your form, or a calculated field in a
query:

=CalcMixedFraction([YourTextField])

Place the function in a module (from the Module database window tab).

Public Function CalcMixedFraction(strMixed As String) As Single
On Error Resume Next
Dim i As Integer

' Declare undimensioned array.
' The Split function will dynamically scope it to 2 elements.
Dim strMyArray() As String

CalcMixedFraction = 0

' This code assumes a hyphen delimiter. If the field contains only a
whole part,
' it will calculate correctly, but if the user enters a whole part, a
space, and the
' fractional part, the function will calculate zero. My preference
would be to use
' the textbox's BeforeUpdate event to ensure the value is in the proper

' format, however, if you wish to permit either a hyphen or a space
' delimiter, include the optional code below in your function.

strMyArray = Split(strMixed, "-")
For i = 0 To UBound(strMyArray)
CalcMixedFraction = CalcMixedFraction + Eval(strMyArray(i))
Next i

' Optional code to permit space delimiter
If CalcMixedFraction = 0 Then
strMyArray = Split(strMixed, " ")
For i = 0 To UBound(strMyArray)
CalcMixedFraction = CalcMixedFraction + Eval(strMyArray(i))
Next i
End If

End Function

Note that there is no error-checking, so you may have to do some testing to
ensure it handles whatever the user might throw in there. Alternatively, use
the BeforeUpdate event to ensure data in the correct format is entered.

Hope that helps.
Sprinks
 
Back
Top