Can you format numbers in Access as a fraction, like in Excel

[woods in me]

is it possible to format a table column in Access as a fraction, like in
Excel. is so how?

 

[Pieter Wijnen]

you definitly can't do that in Excel... (no enforcment what so ever)
you can however force decimals in access using the validation rule
ie
<1 and > 0

Pieter

 

[Clif McIrvin]

I'm curious to see what the experts have to say about this.

I did some testing, and came up with setting a reference to the Excel
library and then referencing the Worksheet function TEXT. From the
Access VBA Immediate Window in 2003:

======== Begin Copy
?Excel.WorksheetFunction.Text(8.33,"# ?/?")
8 1/3
======== End Copy

so -- it looks like you could use VBA code in a form or report and
directly use the Excel function; or you could write your own formatting
routine in VBA <g>

 

[Arvin Meyer [MVP]]

is it possible to format a table column in Access as a fraction, like in
Excel. is so how?

No. You would use a string data type, the use a function like the following
in the AfterUpdate event of a form's text box:

Use it like:

Sub txtMyText_AfterUpdate()
txtMyText = FractionIt(txtMyText)
End Sub


Public Function FractionIt(dblNumIn As Double) As String
'====================================================================
' Name: FractionIt
' Purpose: Converts a double into a string representing a rounded fraction
' Inputs: dblNumIn As Double
' Returns: String
' Author: Arvin Meyer
' Date: June 22, 2001
' Comment: Rounds down from 1/64 over
'====================================================================
On Error GoTo Err_FractionIt

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

If dblNumIn < 0 Then
strSign = "-"
dblNumIn = dblNumIn * -1
Else
strSign = " "
End If

strWholeNum = Fix([dblNumIn])

dblRem = [dblNumIn] - [strWholeNum]

Select Case dblRem
Case 0
strFrac = ""
Case Is < 0.046875
strFrac = "1/32"
Case Is < 0.078125
strFrac = "1/16"
Case Is < 0.109375
strFrac = "3/32"
Case Is < 0.140625
strFrac = "1/8"
Case Is < 0.171875
strFrac = "5/32"
Case Is < 0.203125
strFrac = "3/16"
Case Is < 0.234375
strFrac = "7/32"
Case Is < 0.265625
strFrac = "1/4"
Case Is < 0.296875
strFrac = "9/32"
Case Is < 0.328125
strFrac = "5/16"
Case Is < 0.359375
strFrac = "11/32"
Case Is < 0.390625
strFrac = "3/8"
Case Is < 0.421875
strFrac = "13/32"
Case Is < 0.453125
strFrac = "7/16"
Case Is < 0.484375
strFrac = "15/32"
Case Is < 0.515625
strFrac = "1/2"
Case Is < 0.546875
strFrac = "17/32"
Case Is < 0.578125
strFrac = "9/16"
Case Is < 0.609375
strFrac = "19/32"
Case Is < 0.640625
strFrac = "5/8"
Case Is < 0.671875
strFrac = "21/32"
Case Is < 0.703125
strFrac = "11/16"
Case Is < 0.734375
strFrac = "23/32"
Case Is < 0.765625
strFrac = "3/4"
Case Is < 0.796875
strFrac = "25/32"
Case Is < 0.828125
strFrac = "13/16"
Case Is < 0.859375
strFrac = "27/32"
Case Is < 0.890625
strFrac = "7/8"
Case Is < 0.921875
strFrac = "29/32"
Case Is < 0.953125
strFrac = "15/16"
Case Is < 0.984375
strFrac = "31/32"
Case Is < 1
strFrac = "1"
End Select

If strFrac = "1" Then
FractionIt = strSign & (strWholeNum + 1)
Else
FractionIt = strSign & 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

 

[Clif McIrvin]

Arvin, I'm having a bit of trouble following how this would work (Nice,
simple code in the function, BTW.)

Questions below:

--
Clif
Still learning Access 2003

No. You would use a string data type, the use a function like the
following in the AfterUpdate event of a form's text box:

Use it like:

Sub txtMyText_AfterUpdate()
txtMyText = FractionIt(txtMyText)
End Sub


Public Function FractionIt(dblNumIn As Double) As String

<big snip>

Isn't there a type conflict between the dblNumIn as Double and the
txtMyText in the call?

Also, are you saying that Access will recognize a string like "8 3/4" as
the numerical value 8.75? Or, perhaps more properly, it will correctly
do a type conversion form 8 3/4 to 8.75?

In the brief testing I just did (2003 SP3) the type conversion ended up
with two numbers: 8, and 0.75 (?8 3/4 in the debug window, ?"8 3.4" did
no conversion.)

I tried defining both a string and variant variable and calling your
FractionIt and got a type mismatch error both times.

 

[Arvin Meyer [MVP]]

Arvin, I'm having a bit of trouble following how this would work (Nice,
simple code in the function, BTW.)

Questions below:

Isn't there a type conflict between the dblNumIn as Double and the
txtMyText in the call?

No, because the function returns a string. Try this in the Immediate (debug)
window:

? FractionIt(8.75)
8 3/4

Also, are you saying that Access will recognize a string like "8 3/4" as
the numerical value 8.75? Or, perhaps more properly, it will correctly do
a type conversion form 8 3/4 to 8.75?

No, the reverse. The function returns a string from a mixed decimal.

In the brief testing I just did (2003 SP3) the type conversion ended up
with two numbers: 8, and 0.75 (?8 3/4 in the debug window, ?"8 3.4" did no
conversion.)

I tried defining both a string and variant variable and calling your
FractionIt and got a type mismatch error both times.

Here is a function to return a double from a string. There is no error
handling, and I've only tested it with ("8 3/4" and with "8-3/4"):


Function Fraction2Number(strFraction As String) As Double
Dim intIn As Integer

intIn = InStr(1, strFraction, "-")
If intIn = 0 Then
intIn = InStr(1, strFraction, " ")
End If
If intIn = 0 Then
' They either input only a whole number or an invalid separator.
' A whole number is OK.
If Len(Format(Val(strFraction)) = Len(Trim(strFraction))) Then
Fraction2Number = Val(strFraction)
Else
Fraction2Number = 0
End If
ElseIf Right(Trim(strFraction), 1) = "-" Then
' They input whole number with a dash and nothing following
Fraction2Number = Val(strFraction)
ElseIf InStr(1, strFraction, "/") = 0 Then
' There was a dash or space followed by something but it wasn't a
fraction.
Fraction2Number = 0
Else
Fraction2Number = Val(Left(strFraction, intIn - 1)) +
Eval(Trim(Mid(strFraction, intIn + 1)))
End If

End Function

 

[Clif McIrvin]

Yeah, that would work. Assuming Excel standard formatted fractions,
this seems to work as well (I kept your test for '-' as a separator, and
has even less error checking than your function)

Function Fraction2Number(strFraction As String) As Double
Dim intIn As Integer

intIn = InStr(1, strFraction, "-")
If intIn = 0 Then
intIn = InStr(1, strFraction, " ")
End If
If intIn > 0 Then
' Separator found, change to +
Mid(strFraction, intIn, 1) = "+"
End If
Fraction2Number = Eval(strFraction)

End Function

If passing a string literal, it requires the double quotes:

?fraction2number("8 3/4")
8.75


--
Clif
Still learning Access 2003

 

[Arvin Meyer [MVP]]

If passing a string literal, it requires the double quotes:

?fraction2number("8 3/4")
8.75

As does any string. Perhaps it would be a good idea to test for Chr(34)
first.

 

[woods in me]

Thank you very much for your responces... this has been most helpful.