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. |
|
|
Thread Tools | Display Modes |
#12
|
|||
|
|||
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
|
|||
|
|||
how do i set access to use fractions and add/multiply them?
|
#14
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|