how do i set access to use fractions and add/multiply them?

[KevinKBM]

I need to use fractions in a Access Form to calculate sq foot and other board
dimentions. can't find anything in the help file or settings. I need to ba
able to add, multiply and divide them.

 

[Douglas J. Steele]

There's no way built into Access.

I suppose it might be possible to create functions that look at the
character value of the fractions (for example, ¼ has an Ascii value of 188,
½ has a value of 189 and ¾ has a value of 190) and translates them to the
appropriate value, but that's very limited.

How were you intended to get the fractions into Access?

 

[KevinKBM]

It's for a order form for doors my company produces. all measurments are in
inches. if i knew more about VBA i'm certin a bit of code could solve this
issue but programming in VBA escapes me and this is due end of week.

Thanks for your reply

 

[Klatuu]

Here is something I found on the internet by googling "convert decimal to
fraction in VB"
I played with it and it is okay for even things .125 returns 1/18, etc, but
for 1/3 I tried .67, .667, etc. and got useless results. I tried doing 1/3
in VBA and passing it the number results from that and it went away. I got
tired of waiting and canceled it.

 

[Steve]

TblFractionPart
FractionPartID
FractionPart
DecimalOfFractionPart

TblFractionMeasurement
FractionMeasurementID
FractionPartID
FractionMeasurement

TblFractionPart would contain:
1, "/2",.5
2, "/4", .25
3, "/8", .125
4, "/16", .1667

TblFractionMeasurement would contain:
1, 1, 1
2, 2, 1
3, 2, 2
4, 2, 3
5, 3, 1
6, 3, 2
7, 3, 3
8, 3, 4
9, 3, 5
etc to --
26, 4, 15

On your form where you need to enter a dimension, you need a textbox named
wholeinches for entering the whole inches, a combobox named fractionpart
for picking the fraction part, a combobox named fractionmeasurement for
picking the numerator of the fraction, i.e. 15 for 15/16", a textbox named
MyMeasurement to display the measurement and a hidden textbox named
myMeasurementValue to contain the value of the measurement. Note combobox,
fraction part, needs to be based on TblFractionpart and contain all the
fields. I have left it to you to set up the comboboxes.

You then need to put the following in the control source of MyMeasurement:
=[WholeInches] & " " & [FractionMeasurement].Column(2) &
[FractionPart].Column(1)

You then need to put the following in the control source of
MyMeasurementValue:
= [WholeInches] + [FractionMeasurement].Column(2) *
[FractionPart].Column(2)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[KevinKBM]

Thanks Steve this will realy help a lot.
Is there a way to set this as a query? i'm going to have to have about 25
lines on the form and to do this that many times will be a ton of typing. so
if there is a wayto do it as multiple rows if not then no problem. and thanks
again for the help

TblFractionPart
FractionPartID
FractionPart
DecimalOfFractionPart

TblFractionMeasurement
FractionMeasurementID
FractionPartID
FractionMeasurement

TblFractionPart would contain:
1, "/2",.5
2, "/4", .25
3, "/8", .125
4, "/16", .1667

TblFractionMeasurement would contain:
1, 1, 1
2, 2, 1
3, 2, 2
4, 2, 3
5, 3, 1
6, 3, 2
7, 3, 3
8, 3, 4
9, 3, 5
etc to --
26, 4, 15

On your form where you need to enter a dimension, you need a textbox named
wholeinches for entering the whole inches, a combobox named fractionpart
for picking the fraction part, a combobox named fractionmeasurement for
picking the numerator of the fraction, i.e. 15 for 15/16", a textbox named
MyMeasurement to display the measurement and a hidden textbox named
myMeasurementValue to contain the value of the measurement. Note combobox,
fraction part, needs to be based on TblFractionpart and contain all the
fields. I have left it to you to set up the comboboxes.

You then need to put the following in the control source of MyMeasurement:
=[WholeInches] & " " & [FractionMeasurement].Column(2) &
[FractionPart].Column(1)

You then need to put the following in the control source of
MyMeasurementValue:
= [WholeInches] + [FractionMeasurement].Column(2) *
[FractionPart].Column(2)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I need to use fractions in a Access Form to calculate sq foot and other
board
dimentions. can't find anything in the help file or settings. I need to ba
able to add, multiply and divide them.

 

[jahoobob via AccessMonster.com]

Try this in the Lost Focus or AfterUpdate property of the textbox (Text0)
where the fraction is entered:
slash = InStr(Me.Text0, "/")
Me.Text3 = Left(Me.Text0, slash - 1) / Right(Me.Text0, Len(Me.Text0) - slash)
You can change Me.Text3 to a variable or a field in a table if you want to
store it

 

[jahoobob via AccessMonster.com]

BTW, the above converts ANY fraction to a decimal.

 

[Steve]

Show me how you are going to apply a measurement that includes fractions and
maybe I can help you from there. For example, your measurement is 125 5/8",
what are you going to do with it.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Thanks Steve this will realy help a lot.
Is there a way to set this as a query? i'm going to have to have about 25
lines on the form and to do this that many times will be a ton of typing.
so
if there is a wayto do it as multiple rows if not then no problem. and
thanks
again for the help

TblFractionPart
FractionPartID
FractionPart
DecimalOfFractionPart

TblFractionMeasurement
FractionMeasurementID
FractionPartID
FractionMeasurement

TblFractionPart would contain:
1, "/2",.5
2, "/4", .25
3, "/8", .125
4, "/16", .1667

TblFractionMeasurement would contain:
1, 1, 1
2, 2, 1
3, 2, 2
4, 2, 3
5, 3, 1
6, 3, 2
7, 3, 3
8, 3, 4
9, 3, 5
etc to --
26, 4, 15

On your form where you need to enter a dimension, you need a textbox
named
wholeinches for entering the whole inches, a combobox named fractionpart
for picking the fraction part, a combobox named fractionmeasurement for
picking the numerator of the fraction, i.e. 15 for 15/16", a textbox
named
MyMeasurement to display the measurement and a hidden textbox named
myMeasurementValue to contain the value of the measurement. Note
combobox,
fraction part, needs to be based on TblFractionpart and contain all the
fields. I have left it to you to set up the comboboxes.

You then need to put the following in the control source of
MyMeasurement:
=[WholeInches] & " " & [FractionMeasurement].Column(2) &
[FractionPart].Column(1)

You then need to put the following in the control source of
MyMeasurementValue:
= [WholeInches] + [FractionMeasurement].Column(2) *
[FractionPart].Column(2)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I need to use fractions in a Access Form to calculate sq foot and other
board
dimentions. can't find anything in the help file or settings. I need to
ba
able to add, multiply and divide them.

 

[John W. Vinson]

BTW, the above converts ANY fraction to a decimal.

but won't work with the very common syntax "8 3/4" - it will convert a pure
fraction OK, but if it's got a prior integer number you'll need to extract
just the fraction.

John W. Vinson [MVP]

 

[KevinKBM]

going to be calculating board feet, the formula is WxL \ 144 (11 7/8 x 12 7/8
Divided by 144 and the rusult will be shown in a seprit text box . I can send
you the DB if that will help.

Show me how you are going to apply a measurement that includes fractions and
maybe I can help you from there. For example, your measurement is 125 5/8",
what are you going to do with it.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Thanks Steve this will realy help a lot.
Is there a way to set this as a query? i'm going to have to have about 25
lines on the form and to do this that many times will be a ton of typing.
so
if there is a wayto do it as multiple rows if not then no problem. and
thanks
again for the help

TblFractionPart
FractionPartID
FractionPart
DecimalOfFractionPart

TblFractionMeasurement
FractionMeasurementID
FractionPartID
FractionMeasurement

TblFractionPart would contain:
1, "/2",.5
2, "/4", .25
3, "/8", .125
4, "/16", .1667

TblFractionMeasurement would contain:
1, 1, 1
2, 2, 1
3, 2, 2
4, 2, 3
5, 3, 1
6, 3, 2
7, 3, 3
8, 3, 4
9, 3, 5
etc to --
26, 4, 15

On your form where you need to enter a dimension, you need a textbox
named
wholeinches for entering the whole inches, a combobox named fractionpart
for picking the fraction part, a combobox named fractionmeasurement for
picking the numerator of the fraction, i.e. 15 for 15/16", a textbox
named
MyMeasurement to display the measurement and a hidden textbox named
myMeasurementValue to contain the value of the measurement. Note
combobox,
fraction part, needs to be based on TblFractionpart and contain all the
fields. I have left it to you to set up the comboboxes.

You then need to put the following in the control source of
MyMeasurement:
=[WholeInches] & " " & [FractionMeasurement].Column(2) &
[FractionPart].Column(1)

You then need to put the following in the control source of
MyMeasurementValue:
= [WholeInches] + [FractionMeasurement].Column(2) *
[FractionPart].Column(2)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)



I need to use fractions in a Access Form to calculate sq foot and other
board
dimentions. can't find anything in the help file or settings. I need to
ba
able to add, multiply and divide them.

 

[John W. Vinson]

going to be calculating board feet, the formula is WxL \ 144 (11 7/8 x 12 7/8
Divided by 144 and the rusult will be shown in a seprit text box . I can send
you the DB if that will help.

Ok... let's see if I can throw something together.

Assuming you have Text fields [Length] and [Width] containing strings like
"11 7/8" or "4 15/16" try creating a function in a public module:

Public Function FracToNum(strDim As String) As Double
Dim strFrac As String
Dim strNum As String
Dim dblNumerator As Double
Dim dblDenominator As Double
On Error GoTo Proc_Error
If InStr(strDim, " ") > 0 Then ' both number and fraction
strNum = Left(strDim, InStr(strDim, " ") - 1)
strFrac = Mid(strDim, InStr(strDim, " ") + 1)
Else ' only one piece: is it a number or a fraction?
If InStr(strDim, "/") > 0 Then ' just a fraction
strFrac = strDim
strNum = "0"
Else
strFrac = "0/1"
strNum = strDim
End If
End If
dblNumerator = CDbl(Left(strFrac, InStr(strFrac, "/") - 1))
dblDenominator = CDbl(Mid(strFrac, InStr(strFrac, "/") + 1))
FracToNum = CDbl(strNum) + dblNumerator/dblDenominator
Proc_Exit:
Exit Function
Proc_Error:
MsgBox "Error " & Err.Num & " in FracToNum:" & vbCrLf & Err.Description
Resume Proc_Exit
End Function



and as a bonus, found this in my module:



Public Function DecimalToFrac(DecimalIn) As String

'Convert decimal to Fraction

Dim strWholePart As String
Dim varNumerator As Variant
Dim lngDenominator As Long
Dim intX As Integer
strWholePart = Int(DecimalIn)
intX = InStr([DecimalIn], ".")

If intX = 0 Or IsError(Mid([DecimalIn], intX + 1)) Then
DecimalToFrac = strWholePart
Exit Function
End If

varNumerator = Mid(DecimalIn, InStr(DecimalIn, ".") + 1)
lngDenominator = 1 & String(1 * Len(varNumerator), "0")

Do While lngDenominator Mod 5 = 0 And varNumerator Mod 5 = 0
varNumerator = varNumerator / 5
lngDenominator = lngDenominator / 5
Loop

Do While lngDenominator Mod 2 = 0 And varNumerator Mod 2 = 0
varNumerator = varNumerator / 2
lngDenominator = lngDenominator / 2
Loop

DecimalToFrac = strWholePart & " " & varNumerator & "/" & lngDenominator

End Function

John W. Vinson [MVP]

 

[Steve]

Kevin,

Let me know if you can not get John's function to work and I will try and
help you.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

going to be calculating board feet, the formula is WxL \ 144 (11 7/8 x 12
7/8
Divided by 144 and the rusult will be shown in a seprit text box . I can
send
you the DB if that will help.

Ok... let's see if I can throw something together.

Assuming you have Text fields [Length] and [Width] containing strings like
"11 7/8" or "4 15/16" try creating a function in a public module:

Public Function FracToNum(strDim As String) As Double
Dim strFrac As String
Dim strNum As String
Dim dblNumerator As Double
Dim dblDenominator As Double
On Error GoTo Proc_Error
If InStr(strDim, " ") > 0 Then ' both number and fraction
strNum = Left(strDim, InStr(strDim, " ") - 1)
strFrac = Mid(strDim, InStr(strDim, " ") + 1)
Else ' only one piece: is it a number or a fraction?
If InStr(strDim, "/") > 0 Then ' just a fraction
strFrac = strDim
strNum = "0"
Else
strFrac = "0/1"
strNum = strDim
End If
End If
dblNumerator = CDbl(Left(strFrac, InStr(strFrac, "/") - 1))
dblDenominator = CDbl(Mid(strFrac, InStr(strFrac, "/") + 1))
FracToNum = CDbl(strNum) + dblNumerator/dblDenominator
Proc_Exit:
Exit Function
Proc_Error:
MsgBox "Error " & Err.Num & " in FracToNum:" & vbCrLf & Err.Description
Resume Proc_Exit
End Function



and as a bonus, found this in my module:



Public Function DecimalToFrac(DecimalIn) As String

'Convert decimal to Fraction

Dim strWholePart As String
Dim varNumerator As Variant
Dim lngDenominator As Long
Dim intX As Integer
strWholePart = Int(DecimalIn)
intX = InStr([DecimalIn], ".")

If intX = 0 Or IsError(Mid([DecimalIn], intX + 1)) Then
DecimalToFrac = strWholePart
Exit Function
End If

varNumerator = Mid(DecimalIn, InStr(DecimalIn, ".") + 1)
lngDenominator = 1 & String(1 * Len(varNumerator), "0")

Do While lngDenominator Mod 5 = 0 And varNumerator Mod 5 = 0
varNumerator = varNumerator / 5
lngDenominator = lngDenominator / 5
Loop

Do While lngDenominator Mod 2 = 0 And varNumerator Mod 2 = 0
varNumerator = varNumerator / 2
lngDenominator = lngDenominator / 2
Loop

DecimalToFrac = strWholePart & " " & varNumerator & "/" & lngDenominator

End Function

John W. Vinson [MVP]

 

[Douglas J. Steele]

You'd better be offering to do it for free, Steve, since that's the purpose
of these newsgroups.

 

[KevinKBM]

OK I placed the code in a new module and saved it as frac to identify it
easaly, saved and loaded the form getting a error that tells me that i can't
use "/" that it's invalid. the properties of the text box are as follows

Name: with01 (there will be several of these boxes)
Controle Source: Blank
Format: Standard
Decimal Places: 0
Input Mask: Blank
Default Value: Blank
IME Hold: No
IME Mode: No Controle
IME Sentence Mode: None
Validation Rule: Blank
Validation Text: Blank
Status Bar Text: Blank
Enter key Behavure: Default
Allow Auto Correct: Yes

The Text Boxes that will have to use and display fractions are
width01 to 40, height01 to 40, sqftxqty01 to 40, qty01 to 40

that 3rd box gives a rounded up whole number of the total board feet

the math thats involved is
width x height / 144 x quantity = total board feet

then I take that multiply it by the price per sq foot (determined by door
style, profile, edge, glass or no glass, and finish) and get the final item
price.

figured out that part (books are very helpful)
just these fractions are killin me.

thanks again for the help

going to be calculating board feet, the formula is WxL \ 144 (11 7/8 x 12 7/8
Divided by 144 and the rusult will be shown in a seprit text box . I can send
you the DB if that will help.

Ok... let's see if I can throw something together.

Assuming you have Text fields [Length] and [Width] containing strings like
"11 7/8" or "4 15/16" try creating a function in a public module:

Public Function FracToNum(strDim As String) As Double
Dim strFrac As String
Dim strNum As String
Dim dblNumerator As Double
Dim dblDenominator As Double
On Error GoTo Proc_Error
If InStr(strDim, " ") > 0 Then ' both number and fraction
strNum = Left(strDim, InStr(strDim, " ") - 1)
strFrac = Mid(strDim, InStr(strDim, " ") + 1)
Else ' only one piece: is it a number or a fraction?
If InStr(strDim, "/") > 0 Then ' just a fraction
strFrac = strDim
strNum = "0"
Else
strFrac = "0/1"
strNum = strDim
End If
End If
dblNumerator = CDbl(Left(strFrac, InStr(strFrac, "/") - 1))
dblDenominator = CDbl(Mid(strFrac, InStr(strFrac, "/") + 1))
FracToNum = CDbl(strNum) + dblNumerator/dblDenominator
Proc_Exit:
Exit Function
Proc_Error:
MsgBox "Error " & Err.Num & " in FracToNum:" & vbCrLf & Err.Description
Resume Proc_Exit
End Function



and as a bonus, found this in my module:



Public Function DecimalToFrac(DecimalIn) As String

'Convert decimal to Fraction

Dim strWholePart As String
Dim varNumerator As Variant
Dim lngDenominator As Long
Dim intX As Integer
strWholePart = Int(DecimalIn)
intX = InStr([DecimalIn], ".")

If intX = 0 Or IsError(Mid([DecimalIn], intX + 1)) Then
DecimalToFrac = strWholePart
Exit Function
End If

varNumerator = Mid(DecimalIn, InStr(DecimalIn, ".") + 1)
lngDenominator = 1 & String(1 * Len(varNumerator), "0")

Do While lngDenominator Mod 5 = 0 And varNumerator Mod 5 = 0
varNumerator = varNumerator / 5
lngDenominator = lngDenominator / 5
Loop

Do While lngDenominator Mod 2 = 0 And varNumerator Mod 2 = 0
varNumerator = varNumerator / 2
lngDenominator = lngDenominator / 2
Loop

DecimalToFrac = strWholePart & " " & varNumerator & "/" & lngDenominator

End Function

John W. Vinson [MVP]

 

[John W. Vinson]

Let me know if you can not get John's function to work and I will try and
help you.

I'd be glad to help with my own code too.

John W. Vinson [MVP]

 

[John W. Vinson]

OK I placed the code in a new module and saved it as frac to identify it
easaly, saved and loaded the form getting a error that tells me that i can't
use "/" that it's invalid. the properties of the text box are as follows

That isn't the error message. What is?

What expression are you using on the form to call the function?

John W. Vinson [MVP]

 

[KevinKBM]

the error reads
"The value you entered isn't valid for this field.
for exsample, you may have entered text in a numeric field or a number that
is larger then the FieldSize setting permits"

 

[KevinKBM]

STOP THE PRESSES!!

Ok i now have the text boxes accepting fractions.
and that gave me a idea.

in After Update will it work if i had it convert the fraction (in text) to
decimal, then send that to a query to do the math in and then have that
report the rounded up total to the final box?

that should make things far more simple.
the converter will have to read a text based fraction, in the format of
(##/##)

think that will work? and if so, how do i do it?
thats everybody you have all be most helpful

 

[Steve]

Is that a threat, Doug? And what are you going to do if it's not for free?

By the way, do you write articles for Smart Access for free?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)