I am using Access to price out Corian vanity tops. Its working great, except that I need to input thye sizes in Decimals..Is there a way I can use fractions?? I know in Excel there is a command that I can change to convert it to a fraction..Is there a way in Access??
Converting Decimals to Fractions
- Thread starter robbie9
- Start date
R. Hicks
AWF VIP
- Local time
- Today, 05:44
- Joined
- Dec 23, 1999
- Messages
- 619
I have a function that will convert a decimal number into a fraction, but the output is a string, not a number. The output is accurate to 1/128th of an inch.
'*******************Begin Code******************
Public Function DecimalToFraction(x)
Dim Temp As String
Dim Fixed As Double
If (VarType(x) < 2) Or (VarType(x) > 6) Then
DecimalToFraction = x
Else
x = Abs(x)
Fixed = Int(x)
If Fixed > 0 Then
Temp = str(Fixed)
End If
Select Case x - Fixed
Case Is < 0.007813
If Fixed > 0 Then
Temp = Temp
Else
Temp = str(x)
End If
Case 0.007813 To 0.023438
Temp = Temp + " 1/64"
Case 0.023438 To 0.039063
Temp = Temp + " 1/32"
Case 0.039063 To 0.054688
Temp = Temp + " 3/64"
Case 0.054688 To 0.070313
Temp = Temp + " 1/16"
Case 0.070313 To 0.085938
Temp = Temp + " 5/64"
Case 0.085938 To 0.101563
Temp = Temp + " 3/32"
Case 0.101563 To 0.117188
Temp = Temp + " 7/64"
Case 0.117188 To 0.132813
Temp = Temp + " 1/8"
Case 0.132813 To 0.148438
Temp = Temp + " 9/64"
Case 0.148438 To 0.164063
Temp = Temp + " 5/32"
Case 0.164063 To 0.179688
Temp = Temp + " 11/64"
Case 0.179688 To 0.195313
Temp = Temp + " 3/16"
Case 0.195313 To 0.210938
Temp = Temp + " 13/64"
Case 0.210938 To 0.226563
Temp = Temp + " 7/32"
Case 0.226563 To 0.242188
Temp = Temp + " 15/64"
Case 0.242188 To 0.257813
Temp = Temp + " 1/4"
Case 0.257813 To 0.273438
Temp = Temp + " 17/64"
Case 0.273438 To 0.289063
Temp = Temp + " 9/32"
Case 0.289063 To 0.304688
Temp = Temp + " 19/64"
Case 0.304688 To 0.320313
Temp = Temp + " 5/16"
Case 0.320313 To 0.335938
Temp = Temp + " 21/64"
Case 0.335938 To 0.351563
Temp = Temp + " 11/32"
Case 0.351563 To 0.367188
Temp = Temp + " 23/64"
Case 0.367188 To 0.382813
Temp = Temp + " 3/8"
Case 0.382813 To 0.398438
Temp = Temp + " 25/64"
Case 0.398438 To 0.414063
Temp = Temp + " 13/32"
Case 0.414063 To 0.429688
Temp = Temp + " 27/64"
Case 0.429688 To 0.445313
Temp = Temp + " 7/16"
Case 0.445313 To 0.460938
Temp = Temp + " 29/64"
Case 0.460938 To 0.476563
Temp = Temp + " 15/32"
Case 0.476563 To 0.492188
Temp = Temp + " 31/64"
Case 0.492188 To 0.507813
Temp = Temp + " 1/2"
Case 0.507813 To 0.523438
Temp = Temp + " 33/64"
Case 0.523438 To 0.539063
Temp = Temp + " 17/32"
Case 0.539063 To 0.554688
Temp = Temp + " 35/64"
Case 0.554688 To 0.570313
Temp = Temp + " 9/16"
Case 0.570313 To 0.585938
Temp = Temp + " 37/64"
Case 0.585938 To 0.601563
Temp = Temp + " 19/32"
Case 0.601563 To 0.617188
Temp = Temp + " 39/64"
Case 0.617188 To 0.632813
Temp = Temp + " 5/8"
Case 0.632813 To 0.648438
Temp = Temp + " 41/64"
Case 0.648438 To 0.664063
Temp = Temp + " 21/32"
Case 0.664063 To 0.679688
Temp = Temp + " 43/64"
Case 0.679688 To 0.695313
Temp = Temp + " 11/16"
Case 0.695313 To 0.710938
Temp = Temp + " 45/64"
Case 0.710938 To 0.726563
Temp = Temp + " 23/32"
Case 0.726563 To 0.742188
Temp = Temp + " 47/64"
Case 0.742188 To 0.757813
Temp = Temp + " 3/4"
Case 0.757813 To 0.773438
Temp = Temp + " 49/64"
Case 0.773438 To 0.789063
Temp = Temp + " 25/32"
Case 0.789063 To 0.804688
Temp = Temp + " 51/64"
Case 0.804688 To 0.820313
Temp = Temp + " 13/16"
Case 0.820313 To 0.835938
Temp = Temp + " 53/64"
Case 0.835938 To 0.851563
Temp = Temp + " 27/32"
Case 0.851563 To 0.867188
Temp = Temp + " 55/64"
Case 0.867188 To 0.882813
Temp = Temp + " 7/8"
Case 0.882813 To 0.898438
Temp = Temp + " 57/64"
Case 0.898438 To 0.914063
Temp = Temp + " 29/32"
Case 0.914063 To 0.929688
Temp = Temp + " 59/64"
Case 0.929688 To 0.945313
Temp = Temp + " 15/16"
Case 0.945313 To 0.960938
Temp = Temp + " 61/64"
Case 0.960938 To 0.976563
Temp = Temp + " 31/32"
Case 0.976563 To 0.992188
Temp = Temp + " 63/64"
Case Is > 0.992188
Temp = str(Int(x) + 1)
End Select
DecimalToFraction = Temp
End If
End Function
'*******************End Code******************
HTH
RDH
[This message has been edited by R. Hicks (edited 02-11-2001).]
'*******************Begin Code******************
Public Function DecimalToFraction(x)
Dim Temp As String
Dim Fixed As Double
If (VarType(x) < 2) Or (VarType(x) > 6) Then
DecimalToFraction = x
Else
x = Abs(x)
Fixed = Int(x)
If Fixed > 0 Then
Temp = str(Fixed)
End If
Select Case x - Fixed
Case Is < 0.007813
If Fixed > 0 Then
Temp = Temp
Else
Temp = str(x)
End If
Case 0.007813 To 0.023438
Temp = Temp + " 1/64"
Case 0.023438 To 0.039063
Temp = Temp + " 1/32"
Case 0.039063 To 0.054688
Temp = Temp + " 3/64"
Case 0.054688 To 0.070313
Temp = Temp + " 1/16"
Case 0.070313 To 0.085938
Temp = Temp + " 5/64"
Case 0.085938 To 0.101563
Temp = Temp + " 3/32"
Case 0.101563 To 0.117188
Temp = Temp + " 7/64"
Case 0.117188 To 0.132813
Temp = Temp + " 1/8"
Case 0.132813 To 0.148438
Temp = Temp + " 9/64"
Case 0.148438 To 0.164063
Temp = Temp + " 5/32"
Case 0.164063 To 0.179688
Temp = Temp + " 11/64"
Case 0.179688 To 0.195313
Temp = Temp + " 3/16"
Case 0.195313 To 0.210938
Temp = Temp + " 13/64"
Case 0.210938 To 0.226563
Temp = Temp + " 7/32"
Case 0.226563 To 0.242188
Temp = Temp + " 15/64"
Case 0.242188 To 0.257813
Temp = Temp + " 1/4"
Case 0.257813 To 0.273438
Temp = Temp + " 17/64"
Case 0.273438 To 0.289063
Temp = Temp + " 9/32"
Case 0.289063 To 0.304688
Temp = Temp + " 19/64"
Case 0.304688 To 0.320313
Temp = Temp + " 5/16"
Case 0.320313 To 0.335938
Temp = Temp + " 21/64"
Case 0.335938 To 0.351563
Temp = Temp + " 11/32"
Case 0.351563 To 0.367188
Temp = Temp + " 23/64"
Case 0.367188 To 0.382813
Temp = Temp + " 3/8"
Case 0.382813 To 0.398438
Temp = Temp + " 25/64"
Case 0.398438 To 0.414063
Temp = Temp + " 13/32"
Case 0.414063 To 0.429688
Temp = Temp + " 27/64"
Case 0.429688 To 0.445313
Temp = Temp + " 7/16"
Case 0.445313 To 0.460938
Temp = Temp + " 29/64"
Case 0.460938 To 0.476563
Temp = Temp + " 15/32"
Case 0.476563 To 0.492188
Temp = Temp + " 31/64"
Case 0.492188 To 0.507813
Temp = Temp + " 1/2"
Case 0.507813 To 0.523438
Temp = Temp + " 33/64"
Case 0.523438 To 0.539063
Temp = Temp + " 17/32"
Case 0.539063 To 0.554688
Temp = Temp + " 35/64"
Case 0.554688 To 0.570313
Temp = Temp + " 9/16"
Case 0.570313 To 0.585938
Temp = Temp + " 37/64"
Case 0.585938 To 0.601563
Temp = Temp + " 19/32"
Case 0.601563 To 0.617188
Temp = Temp + " 39/64"
Case 0.617188 To 0.632813
Temp = Temp + " 5/8"
Case 0.632813 To 0.648438
Temp = Temp + " 41/64"
Case 0.648438 To 0.664063
Temp = Temp + " 21/32"
Case 0.664063 To 0.679688
Temp = Temp + " 43/64"
Case 0.679688 To 0.695313
Temp = Temp + " 11/16"
Case 0.695313 To 0.710938
Temp = Temp + " 45/64"
Case 0.710938 To 0.726563
Temp = Temp + " 23/32"
Case 0.726563 To 0.742188
Temp = Temp + " 47/64"
Case 0.742188 To 0.757813
Temp = Temp + " 3/4"
Case 0.757813 To 0.773438
Temp = Temp + " 49/64"
Case 0.773438 To 0.789063
Temp = Temp + " 25/32"
Case 0.789063 To 0.804688
Temp = Temp + " 51/64"
Case 0.804688 To 0.820313
Temp = Temp + " 13/16"
Case 0.820313 To 0.835938
Temp = Temp + " 53/64"
Case 0.835938 To 0.851563
Temp = Temp + " 27/32"
Case 0.851563 To 0.867188
Temp = Temp + " 55/64"
Case 0.867188 To 0.882813
Temp = Temp + " 7/8"
Case 0.882813 To 0.898438
Temp = Temp + " 57/64"
Case 0.898438 To 0.914063
Temp = Temp + " 29/32"
Case 0.914063 To 0.929688
Temp = Temp + " 59/64"
Case 0.929688 To 0.945313
Temp = Temp + " 15/16"
Case 0.945313 To 0.960938
Temp = Temp + " 61/64"
Case 0.960938 To 0.976563
Temp = Temp + " 31/32"
Case 0.976563 To 0.992188
Temp = Temp + " 63/64"
Case Is > 0.992188
Temp = str(Int(x) + 1)
End Select
DecimalToFraction = Temp
End If
End Function
'*******************End Code******************
HTH
RDH
[This message has been edited by R. Hicks (edited 02-11-2001).]
R. Hicks
AWF VIP
- Local time
- Today, 05:44
- Joined
- Dec 23, 1999
- Messages
- 619
I guess I should have explained how to use the function.
Copy the function from the earlier post into a new module. Give this module a name like Fraction. You can then use the function as you would any other built-in function.
I'm not sure where you need tis info. So here are a couple of examples:
To see conversion on a form, insert a txtbox to receive the info on the form. Then for the control source of the txtbox put:
=DecimalToFraction([Length])
(where "Length" is the name of the txtbox that contains the Decimal equivalent)
If you need to convert the Decimal in a Query, use an expression in the query:
Exp1:
ecimalToFraction([Length])
(where "Length" is the field in the query that contains the Decimal equivalent)
Basically you use the function by substituting "x" in the function's argument for your decimal value reference.
DecimalToFraction(x)
HTH
RDH
[This message has been edited by R. Hicks (edited 02-12-2001).]
Copy the function from the earlier post into a new module. Give this module a name like Fraction. You can then use the function as you would any other built-in function.
I'm not sure where you need tis info. So here are a couple of examples:
To see conversion on a form, insert a txtbox to receive the info on the form. Then for the control source of the txtbox put:
=DecimalToFraction([Length])
(where "Length" is the name of the txtbox that contains the Decimal equivalent)
If you need to convert the Decimal in a Query, use an expression in the query:
Exp1:
(where "Length" is the field in the query that contains the Decimal equivalent)
Basically you use the function by substituting "x" in the function's argument for your decimal value reference.
DecimalToFraction(x)
HTH
RDH
[This message has been edited by R. Hicks (edited 02-12-2001).]
Now you really confused me.. So I don't have to change anything in the Formula?? After I input the Length, How does the formula get the Number??? Am I calling it up in the wrong spot?? or Do I need to change X to something??
R. Hicks
AWF VIP
- Local time
- Today, 05:44
- Joined
- Dec 23, 1999
- Messages
- 619
It all depends on where the function is used. If your db is in Access 97, and not sensitive data, email a copy of it to me. Also let know where you want the fraction to show. I will see if I can give you a hand. If not, try to explain where you want the fraction to show (form, report...txtbox...ect) maybe I can build a sample db to incorporate the function to show you how to use it.
HTH
RDH
HTH
RDH
The DB is written in Access 2000.. So I can send it to you if you can use it.. What I am trying to do is, when I am Pricing the VAnity top, The only way I can Calculate the price is buy inputing the size with a Decimal (22.5 43.5 ect..) The problem is when the worksheet gets printed out, The guys in the shop have a tough time with the Decimals..We don't exactly have Rocket Scientists in the shop..So I would like it when I print out the sheet, it converts it to a fraction...
R. Hicks
AWF VIP
- Local time
- Today, 05:44
- Joined
- Dec 23, 1999
- Messages
- 619
Try either of thr following Email addresses:
ricky@athree.com
rdhicks@mindspring.com
HTH
RDH
[This message has been edited by R. Hicks (edited 02-19-2001).]
ricky@athree.com
rdhicks@mindspring.com
HTH
RDH
[This message has been edited by R. Hicks (edited 02-19-2001).]
- Local time
- Today, 11:44
- Joined
- Jul 9, 2003
- Messages
- 16,872
This VBA code to convert decimal to fraction did not make sense to me until I found this:-
https://stackoverflow.com/questions/5068951/what-do-lt-and-gt-stand-for
You need to replace this:- > with greater than ">" and this:- < with less than "<"
https://stackoverflow.com/questions/5068951/what-do-lt-and-gt-stand-for
You need to replace this:- > with greater than ">" and this:- < with less than "<"
- Local time
- Today, 11:44
- Joined
- Jul 9, 2003
- Messages
- 16,872
I haven't made my mind up yet. I found an alternative method:-
https://social.msdn.microsoft.com/F...convert-decimal-to-fraction?forum=Vsexpressvb
thought I would give them both a try and see which one I preferred.
Now all I need is a way to convert kilograms into stones and pounds!
I might be able to adapt the vbs function, if I can get it to work at all that is!
https://social.msdn.microsoft.com/F...convert-decimal-to-fraction?forum=Vsexpressvb
thought I would give them both a try and see which one I preferred.
Now all I need is a way to convert kilograms into stones and pounds!
I might be able to adapt the vbs function, if I can get it to work at all that is!
- Local time
- Today, 06:44
- Joined
- Feb 19, 2002
- Messages
- 45,336
As you can see, fractions don't convert cleanly to decimals. There is a range. Rather than using a case statement, I would create a table that includes
StringFraction
LowDecimal
HighDecimal
SaveAsDecimal
The StringFraction is 1/2, 3/4, etc.
LowDecimal is the low end of the range and HighDecimal is the high end of the range. These numbers are used to decide what StringFraction will be displayed for any given value in the range.
SaveAsDecimal is the PK of the record and the value you want saved when the user picks the StringFraction out of a combobox.
StringFraction
LowDecimal
HighDecimal
SaveAsDecimal
The StringFraction is 1/2, 3/4, etc.
LowDecimal is the low end of the range and HighDecimal is the high end of the range. These numbers are used to decide what StringFraction will be displayed for any given value in the range.
SaveAsDecimal is the PK of the record and the value you want saved when the user picks the StringFraction out of a combobox.
D
Deleted member 147267
Guest
You are measuring in fractions, yes? Or does your measuring device measure in decimal? If not, then isn't it far easier to input the data as measured (shop guys will like that) and convert to decimal for pricing? Isn't it far easier to divide 11 by 16 and get 0.6875 than it is to do all this work to get 11/16 out of .6875?
Last edited by a moderator:
- Local time
- Today, 06:44
- Joined
- Feb 19, 2002
- Messages
- 45,336
It's not my fault 
Uncle replied to a 18 year old post and I got caught up in the madness.
Uncle replied to a 18 year old post and I got caught up in the madness.
D
Deleted member 147267
Guest
The blind leading the blind?
- Local time
- Today, 11:44
- Joined
- Jul 9, 2003
- Messages
- 16,872
It's not my fault
My first reply was to offer some extra information that some might find useful! I don't really know what happened after that!
- Local time
- Today, 11:44
- Joined
- Jul 9, 2003
- Messages
- 16,872
I think Pat's hit the nail on the head as usual!
I am also guilty of a rookie mistake in not explaining the problem properly!
The goal is a table of measurements in centimetres and feet and inches, with a key value stored which will be centimetres. This field will provide the basis for any calculations.
On the form the user will have an option button to select the display of the measurement in either centimetres or feet and inches.
I've got all this working, both lists, the feet and inches, and pounds and stones, and the switch-over from decimal to English. However the feet and inch list really needs quarter inches added to it, so that it doesn't have repeating values that are exactly the same. I think the function provided by R Hicks will cure this...
I've done all sorts of calculations in a spreadsheet for both, converting centimetres to feet and inches and kilograms to stones and pounds.
I have posted my current Google Spreadsheet, for anyone to to play around with, if you have the time and inclination.
Basically you use modulus to handle the remainders, but it doesn't seem to matter how small a fraction you get down to, you can't get a nice list of matching heights. The weights are OK though.
I'm drawn to the case statement with as Pat described, ranges.
I'm not convinced it's impossible to do with a calculation, I think there's something I don't understand about the calculation itself. I reckon a correctly constructed function would probably do it.
Again, as Pat said they need to go in a table, in fact I already have them in a table, but I have numerous entries where I have the centimetres increasing by 1cm and being matched to exactly the same feet and inch measurement several times, usually in groups of 2 or 3. I reckon the solution is to provide quarter-inch measurements and I think that would sort out the problem. but it had me scratching my head somewhat!
Last edited:
