A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #11  
Old June 28th, 2007, 07:12 PM posted to microsoft.public.access.forms
KevinKBM
external usenet poster
 
Posts: 14
Default how do i set access to use fractions and add/multiply them?

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.

"Steve" wrote:

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





"KevinKBM" wrote in message
...
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

"Steve" wrote:

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




"KevinKBM" wrote in message
news 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.






  #12  
Old June 28th, 2007, 09:29 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how do i set access to use fractions and add/multiply them?

On Thu, 28 Jun 2007 11:12:03 -0700, KevinKBM
wrote:

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]
  #13  
Old June 29th, 2007, 02:30 AM posted to microsoft.public.access.forms
Steve[_10_]
external usenet poster
 
Posts: 608
Default how do i set access to use fractions and add/multiply them?

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






"John W. Vinson" wrote in message
...
On Thu, 28 Jun 2007 11:12:03 -0700, KevinKBM
wrote:

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]



  #14  
Old June 29th, 2007, 01:49 PM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default how do i set access to use fractions and add/multiply them?

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Steve" wrote in message
ink.net...
Kevin,

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



  #15  
Old June 29th, 2007, 03:22 PM posted to microsoft.public.access.forms
KevinKBM
external usenet poster
 
Posts: 14
Default how do i set access to use fractions and add/multiply them?

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 Behavu 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



"John W. Vinson" wrote:

On Thu, 28 Jun 2007 11:12:03 -0700, KevinKBM
wrote:

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]

  #16  
Old June 29th, 2007, 05:13 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how do i set access to use fractions and add/multiply them?

On Fri, 29 Jun 2007 01:30:20 GMT, "Steve" wrote:

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]
  #17  
Old June 29th, 2007, 05:14 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default how do i set access to use fractions and add/multiply them?

On Fri, 29 Jun 2007 07:22:01 -0700, KevinKBM
wrote:

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]
  #18  
Old June 29th, 2007, 05:44 PM posted to microsoft.public.access.forms
KevinKBM
external usenet poster
 
Posts: 14
Default how do i set access to use fractions and add/multiply them?

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"

"John W. Vinson" wrote:

On Fri, 29 Jun 2007 07:22:01 -0700, KevinKBM
wrote:

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]

  #19  
Old June 29th, 2007, 06:42 PM posted to microsoft.public.access.forms
KevinKBM
external usenet poster
 
Posts: 14
Default how do i set access to use fractions and add/multiply them?

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
  #20  
Old June 29th, 2007, 07:27 PM posted to microsoft.public.access.forms
Steve[_10_]
external usenet poster
 
Posts: 608
Default how do i set access to use fractions and add/multiply them?

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






"Douglas J. Steele" wrote in message
...
You'd better be offering to do it for free, Steve, since that's the
purpose of these newsgroups.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Steve" wrote in message
ink.net...
Kevin,

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





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.