converting decimals to fractions

[Deb]

Hi,
I need to display decimal figures as fractions in my
table. Eg 0.50 as 1/2 etc and only need quarters and
thirds to be converted. I'd like to retain the original
decimals for calculation purposes, otherwise I'd simply
use a text box instead of messing around.

I've searched all over but can't find the solution. Hope
someone can help.

 

[Cynthia]

You might try using a text box and the CDec function to
convert quarters and thirds to decimals.

 

[Steve Schapel]

Deb,

One option is to make a user-defined function in a public module, and
then use this as applicable in a query, or in the controlsource of a
textbox on your form or report. But I am not sure how you will go
with the thirds, since the decimal version is imprecise. Anyway, this
might give you an idea...

Public Function FractionText(SomeNumber As Single) As String
Dim SomeUnits As String
Dim SomeDecimal As Single
Dim PartialUnit As String
If Int(SomeNumber) = 0 Then
SomeUnits = ""
Else
SomeUnits = Format(Int(SomeNumber))
End If
SomeDecimal = SomeNumber - Int(SomeNumber)
Select Case SomeDecimal
Case 0
PartialUnit = ""
Case 0.25
PartialUnit = "¼"
Case 0.5
PartialUnit = "½"
Case 0.75
PartialUnit = "¾"
End Select
FractionText = SomeUnits & PartialUnit
End Function

I have assumed you really wanted ½ and not 1/2 as you put in your
post. But anyway, the actual numerical data is retained unaltered in
your table, for any calculations needed.

- Steve Schapel, Microsoft Access MVP

 

[Deb]

Hey Steve,

Thank you so much for the time and you did get it right
about the halves .

I cut and pasted your code into a new VB module and
associated it with the control source for the column
concerned in my 'form'. Alas I got 'error' wherever the
field contained any data, even in the ones with 0.50 etc.

Are you or anyone else able to assist further? Thanks in
advance.

Deb

 

[Steve Schapel]

Deb,

I have just checked this code in Access 97 and Access 2000, and it
works as advertised for me. You didn't mention how you "associated"
it with your existing data. If you are doing it in a form, you will
have to use this syntax in the control source of the textbox...
=FractionText([NameOfYourNumberField])
.... and then you will have to make sure that the textbox is not named
tha same as the field you are doing the calculation on!

Please post back again if you still have troubles.

- Steve Schapel, Microsoft Access MVP