Hi i am having a little problem with an equation function that was created from all your help previously. The function works fine itself but with a small glitch within it.
Here is the function code. - Public Function fCalcEquation(strEquation As String) As Long
-
-
Dim MyDB As DAO.Database
-
Dim rstParameters As DAO.Recordset
-
Dim intParamPosition As Integer
-
Dim strParameter As String
-
Dim strValue As String
-
Dim blnRepeatLoop As Boolean
-
-
Set MyDB = CurrentDb
-
-
Set rstParameters = MyDB.OpenRecordset("SELECT * FROM parmas")
-
-
With rstParameters
-
Do Until .EOF
-
strParameter = !ParameterShortDesc
-
strValue = CStr(!Value)
-
CheckVal:
-
intParamPosition = InStr(1, strEquation, strParameter)
-
If intParamPosition > 0 Then
-
strEquation = (Left(strEquation, (intParamPosition - 1)) + strValue + Mid(strEquation, intParamPosition + Len(strParameter), Len(strEquation)))
-
blnRepeatLoop = True
-
Else
-
blnRepeatLoop = False
-
End If
-
.MoveNext
-
Loop
-
End With
-
-
rstParameters.Close
-
Set MyDB = Nothing
-
-
fCalcEquation = Eval(strEquation)
-
-
'Note: Explanation
-
-
'A Recordset is based on the Parameters Table (parmas).
-
'The code iterates through each Parameter, seeing if this Parameter exists in the Equation.
-
'If the Parameter is contained within the Equation, its corresponding Value is extracted, and the actual Equation is build with these substituted values in place of the variables.
-
'The inner loop is now repeated if a Parameter is found. If a Parameter is not found, the code falls through and moves to the next Record in the Recordset (Parameter).
-
'When all Parameters have been evaluated against the Equation (.EOF = True) the code ends.
-
-
'*_* Problem if the same Parameter exists more than once, the code explodes!
-
-
'*_* Solution is to rename the Parameter for example; CarcT and Carct_2
-
-
End Function
Here is the list of Parameters. -
ParameterID ParameterShortDesc ParameterLongDesc Value
-
1 Width Width 500
-
2 Depth Depth 700
-
3 Height Height 800
-
4 BBackVoid Base Back Void 50
-
5 WBackVoid Wall Back Void 22
-
6 TBackVoid Tall BackVoid 50
-
7 WShelfSetBack Wall Shelf Set Back 48
-
8 BShelfSetBack Base Shelf Set Back 48
-
9 TShelfSetBack Tall Shelf Set Back 48
-
10 Groove Groove Depth 9
-
11 CarcT Carcass Thickness 18
-
12 DrawerST Drawer Side Thickness 140
-
13 RailT Rail Thickness 120
-
14 BackT Back Thickness 140
-
15 PlinthH Plinth Height 150
-
16 FrameSW Frame Style Width 40
-
17 FrameTH Frame Top Height 40
-
18 FrameBH Frame Bottom Height 40
-
19 FrameRH Frame Rail Height 26
-
20 DoorSW Door Style Width 80
-
22 DoorBH Door Bottom Height 80
-
23 DrawerSH Drawer Side Height 80
-
24 DrawerFH Drawer Front Height 140
-
25 DrawerBH Drawer Back Heigth 140
-
26 DrawerFasciaH Drawer Fascia Height 140
-
27 DrawerDeepFasciaH Drawer Deep Fascia Height
-
224
-
28 Tenon Tenon 9
-
29 Play Play 2
-
30 Adjustment Adjustment 1
-
36 MullionWid Mullion Width 90
-
37 MidRailWid Mid Rail Width 140
-
40 FrameTen Frame Tenon 20
-
41 DoorTen Door Tenon 30
-
42 FrameT Frame Thickness 26
-
43 DoorT Door Panel Thickness 10
-
44 DoorPanelTen Door Panel Tenon 10
-
45 CarcT_2 Carcass Thickness 18
-
46 DoorRH Door Rail Thickness 80
-
47 DoorSWC Door Style Closer 90
-
48 DoorTH Door Top Height 80
Here is an example of the problem i am having;
Equation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPan elTen*2)
When the Equation reaches the DoorTH of the line, it goes through the entire Parameter table and uses the DoorT value instead, is there a away of telling it to match the entire name of DoorTH. The values are different you see and the function errors as it leaves Height-PlinthH-FrameBH-FrameTH-DoorBH-10H+(DoorPanelT en*2).
Could anyone please help me with this issue!
10 2371 ChipR 1,287
Recognized Expert Top Contributor
Have you tried the Replace Function? It could also handle multiple occurrences of the same variable.
Then your loop would just be: - strParameter = !ParameterShortDesc
-
strValue = !Value
-
Replace(strEquation, strParameter, strValue)
-
.MoveNext
Hi Constantine AI. Problem appears to result from using Instr in line 19 (youv'e edited your code since it was posted!!) to find the name of a matching parameter. If two parameters have the same characters up to some point, like DoorT and DoorTH, Instr will match against both for the shorter name. If it finds DoorTH first then it will be used instead of DoorT, hence your error.
If you are using a parameters table it would be better to have an explicit name field for each parameter on which you can do a full match using '=' instead of Instr. In the longer term this will be far more robust, but as it would involve a partial redesign why not just rename one or other parameter something else throughout?
For example, you could change DoorT to Door-T, or DoorTH to Door-TH or anything else that would make sense in the context of your formula. Just make sure that there are no partial matches on more than one parameter resulting from the rename!
-Stewart
PS your edited post shows that you ARE using the full name of the parameter - so why use Instr at all?
NeoPa 32,570
Recognized Expert Moderator MVP
I'm guessing that the line : - Equation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPanelTen*2)
Should really read : - strEquation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPanelTen*2)
and corresponds to the actual value passed to the function.
If that is the case then it is imperative either that you delineate the replaceable items in the string, or you live with the restriction that no replaceable part can include another one wholly within it (as is illustrated in your example). I would suggest delineating your replaceable parts with []. That would leave : - strEquation = [Height]-[PlinthH]-[FrameBH]-[FrameTH]-[DoorBH]-[DoorTH]+([DoorPanelTen]*2)
The code would need to check for the full value including the []. That doesn't mean the table needs to change of course.
ChipR 1,287
Recognized Expert Top Contributor
Good point. The Replace function will still require that no parameter be a subset of another parameter.
Nope no luck i am afraid, the code still thinks DoorT is DoorTH. This is where i put your Replace syntax. Could you tell me if i put it in the right place? - With rstParameters
-
Do Until .EOF
-
strParameter = !ParameterShortDesc
-
strValue = CStr(!Value)
-
CheckVal:
-
intParamPosition = InStr(1, strEquation, strParameter)
-
Replace strEquation, strParameter, strValue
-
If intParamPosition > 0 Then
-
strEquation = (Left(strEquation, (intParamPosition - 1)) + strValue + Mid(strEquation, intParamPosition + Len(strParameter), Len(strEquation)))
-
blnRepeatLoop = True
-
Else
-
blnRepeatLoop = False
-
End If
-
.MoveNext
-
Loop
-
End With
NeoPa 32,570
Recognized Expert Moderator MVP
I would also suggest using the Replace() function. If you weren't aware of it, it does exactly what you need but with far less hassle.
Here is a version of your function changed to use it. - Public Function fCalcEquation(strEquation As String) As Long
-
-
Dim MyDB As DAO.Database
-
-
Set MyDB = CurrentDb
-
With MyDB.OpenRecordset("SELECT * FROM parmas")
-
Do Until .EOF
-
strEquation = Replace(strEquation, _
-
"[" & !ParameterShortDesc & "]", _
-
!Value)
-
.MoveNext
-
Loop
-
End With
-
-
Set MyDB = Nothing
-
-
fCalcEquation = Eval(strEquation)
-
-
'Note: Explanation
-
'All parameters found in [parmas] are replaced where found in strEquation with the corresponding [Value].
-
-
End Function
I hope this is useful.
ChipR 1,287
Recognized Expert Top Contributor NeoPa 32,570
Recognized Expert Moderator MVP
I've dealt with similar issues before Chip ;) Good work yourself anyway :)
NeoPa 32,570
Recognized Expert Moderator MVP
I should also add that I think this function is a really clever example of using the code system in a very flexible way. Good for you Constantine.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Brian Kazian |
last post by:
Here's my problem, and hopefully someone can help me figure out if there is
a good way to do this.
I am writing a program that allows the user to enter an equation in a text
field using pre-existing variables. They then enter numerical values for
these variables, or can tell the program to randomize the values used within
a certain bounds. My problem is taking in this equation they have written
in the text field and converting it into...
|
by: Stud Muffin |
last post by:
Hey
Basically, I'm trying to take objects created in microsoft word using
equation editor (for creating clean looking math/physics equations)
and putting them into some sort of webpage format. But they come out
grossly unalligned and ugly when I try to directly copy and paste into
microsoft frontpage 2000. Few things I could do is place them directly
using x/y coord (which i don't know how to do), or just taking
screenshots and use...
|
by: S. Nurbe |
last post by:
Hi,
How can I program this math. equation:
a1*n1 + a2*n2 + a3*n3 = cos(alpha1)
b1*n1 + b2*n2 + b3*n3 = cos(alpha2)
n1^2 + n2^2 + n3^2 = 1
The problem is, that when you solve for n1, n2 and n3 (anything else
is known) you get really huge results.
So is there maybe another way to compute this?
|
by: Michael MacDonald |
last post by:
I have written a simple program that calculates a running $ total. This
program uses a checkbox to acknowledge that that version of the ticket
is desired and it checks the Qty box to see how many tickets are
desired. Based upon the checkbox being checked and the number of tickets
desired directly affects the running grand total (of course!). OK I
have set where if ticketsDesiredTotal>0 then checkbox.checked = true.
The only problem I...
|
by: w33nie |
last post by:
My table is pretty well complete, but I would prefer it if the value for Points could be turned into a mathematical equation,
and this equation would use the data from the other fields in the table to achieve a value.
I want this done in phpMyAdmin 2.7, if possible, so that when I change the values in other fields, the Points variable changes, due to its equation.
The equation I would want would be something like:
"won * 3 + drawn * 1"...
| |
by: Trev17 |
last post by:
Hello, I am new to C++ and i have tried for several hours to make a program my teacher has given me as a lab. Here is the Lab question:
the roots of the quadratic equation ax^2 + bx + c = 0, a cannot equal 0 are given by the following formula -b + or - square root of (b^2 - 4ac) / 2a.
If b^2 - 4ac = 0 then equation has a single root. if b^2 - 4ac > 0 then equation has two real roots. if b^2 - 4ac < 0 then equation has two complex...
|
by: Deviate |
last post by:
Hi again guys :)
Ok i have this problem, i need to solve the equation phi(x) = 0.1
(where phi is the area under the gaussian normal distribution function)
apparently im supposed to solve it as f(x)=0 , ( phi(x) - 0.1 =0 ) , which can be solved by iteration, for example by using newton-raphson. But i have no idea how to do this. NR needs f(x), f'(x), how can i get that from phi? :s since that function cant be differentiated or...
|
by: kjcheste |
last post by:
I'm trying to have C++ solve an equation I have set up.
The equation is:
angle = (1/16)*(pow(LS,2) + LS*sqrt(pow(LS,2) + pow(16,2) - 1) - 1);
where LS is a number from -1 to 1 by increasing it by 0.0001
I have angle and LS defined as double, when I run this program it tells me that the angle is equal to -1, -0, or 0 no matter what LS is.
I know that the angle should have decimal places, no idea why it keeps giving a number of 1 or...
|
by: phoenix1990 |
last post by:
so i have an entry frame where i want to input an equation, and i need to turn the string into an actual equation in terms of x. so that i can plot it on a canvas.
i already know how to make the entry frame, and how to extract the string equation that was inputed.
the only problem i'm having is converting it into an actual equation.
e.g.
the string input is 'sin(x)'
and i want to turn it into sin(x)
x is defined by another entry frame...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |