By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Complicated Equation Function Problem

100+
P: 129
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcEquation(strEquation As String) As Long
  2.  
  3.     Dim MyDB As DAO.Database
  4.     Dim rstParameters As DAO.Recordset
  5.     Dim intParamPosition As Integer
  6.     Dim strParameter As String
  7.     Dim strValue As String
  8.     Dim blnRepeatLoop As Boolean
  9.  
  10.     Set MyDB = CurrentDb
  11.  
  12.     Set rstParameters = MyDB.OpenRecordset("SELECT * FROM parmas")
  13.  
  14.     With rstParameters
  15.         Do Until .EOF
  16.             strParameter = !ParameterShortDesc
  17.             strValue = CStr(!Value)
  18. CheckVal:
  19.             intParamPosition = InStr(1, strEquation, strParameter)
  20.             If intParamPosition > 0 Then
  21.             strEquation = (Left(strEquation, (intParamPosition - 1)) + strValue + Mid(strEquation, intParamPosition + Len(strParameter), Len(strEquation)))
  22.                 blnRepeatLoop = True
  23.             Else
  24.                 blnRepeatLoop = False
  25.             End If
  26.             .MoveNext
  27.         Loop
  28.     End With
  29.  
  30.     rstParameters.Close
  31.     Set MyDB = Nothing
  32.  
  33.     fCalcEquation = Eval(strEquation)
  34.  
  35.     'Note: Explanation
  36.  
  37.     'A Recordset is based on the Parameters Table (parmas).
  38.     'The code iterates through each Parameter, seeing if this Parameter exists in the Equation.
  39.     '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.
  40.     '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).
  41.     'When all Parameters have been evaluated against the Equation (.EOF = True) the code ends.
  42.  
  43.     '*_* Problem if the same Parameter exists more than once, the code explodes!
  44.  
  45.     '*_* Solution is to rename the Parameter for example; CarcT and Carct_2
  46.  
  47. End Function
Here is the list of Parameters.
Expand|Select|Wrap|Line Numbers
  1. ParameterID  ParameterShortDesc  ParameterLongDesc    Value
  2.    1         Width               Width                  500
  3.    2         Depth               Depth                  700
  4.    3         Height              Height                 800
  5.    4         BBackVoid           Base Back Void          50
  6.    5         WBackVoid           Wall Back Void          22
  7.    6         TBackVoid           Tall BackVoid           50
  8.    7         WShelfSetBack       Wall Shelf Set Back     48
  9.    8         BShelfSetBack       Base Shelf Set Back     48
  10.    9         TShelfSetBack       Tall Shelf Set Back     48
  11.   10         Groove              Groove Depth             9
  12.   11         CarcT               Carcass Thickness       18
  13.   12         DrawerST            Drawer Side Thickness  140
  14.   13         RailT               Rail Thickness         120
  15.   14         BackT               Back Thickness         140
  16.   15         PlinthH             Plinth Height          150
  17.   16         FrameSW             Frame Style Width       40
  18.   17         FrameTH             Frame Top Height        40
  19.   18         FrameBH             Frame Bottom Height     40
  20.   19         FrameRH             Frame Rail Height       26
  21.   20         DoorSW              Door Style Width        80
  22.   22         DoorBH              Door Bottom Height      80
  23.   23         DrawerSH            Drawer Side Height      80
  24.   24         DrawerFH            Drawer Front Height    140
  25.   25         DrawerBH            Drawer Back Heigth     140
  26.   26         DrawerFasciaH       Drawer Fascia Height   140
  27.   27         DrawerDeepFasciaH   Drawer Deep Fascia Height
  28.                                                         224
  29.   28         Tenon               Tenon                    9
  30.   29         Play                Play                     2
  31.   30         Adjustment          Adjustment               1
  32.   36         MullionWid          Mullion Width           90
  33.   37         MidRailWid          Mid Rail Width         140
  34.   40         FrameTen            Frame Tenon             20
  35.   41         DoorTen             Door Tenon              30
  36.   42         FrameT              Frame Thickness         26
  37.   43         DoorT               Door Panel Thickness    10
  38.   44         DoorPanelTen        Door Panel Tenon        10
  39.   45         CarcT_2             Carcass Thickness       18
  40.   46         DoorRH              Door Rail Thickness     80
  41.   47         DoorSWC             Door Style Closer       90
  42.   48         DoorTH              Door Top Height         80
Here is an example of the problem i am having;

Equation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPanelTen*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+(DoorPanelTen*2).

Could anyone please help me with this issue!
Feb 25 '09 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 1,287
Have you tried the Replace Function? It could also handle multiple occurrences of the same variable.

Then your loop would just be:
Expand|Select|Wrap|Line Numbers
  1. strParameter = !ParameterShortDesc 
  2. strValue = !Value
  3. Replace(strEquation, strParameter, strValue)
  4. .MoveNext
Feb 25 '09 #2

Expert Mod 2.5K+
P: 2,545
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?
Feb 25 '09 #3

NeoPa
Expert Mod 15k+
P: 31,418
I'm guessing that the line :
Expand|Select|Wrap|Line Numbers
  1. Equation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPanelTen*2)
Should really read :
Expand|Select|Wrap|Line Numbers
  1. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 25 '09 #4

Expert 100+
P: 1,287
Good point. The Replace function will still require that no parameter be a subset of another parameter.
Feb 25 '09 #5

100+
P: 129
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?

Expand|Select|Wrap|Line Numbers
  1.     With rstParameters
  2.         Do Until .EOF
  3.             strParameter = !ParameterShortDesc
  4.             strValue = CStr(!Value)
  5. CheckVal:
  6.             intParamPosition = InStr(1, strEquation, strParameter)
  7.             Replace strEquation, strParameter, strValue
  8.             If intParamPosition > 0 Then
  9.             strEquation = (Left(strEquation, (intParamPosition - 1)) + strValue + Mid(strEquation, intParamPosition + Len(strParameter), Len(strEquation)))
  10.                 blnRepeatLoop = True
  11.             Else
  12.                 blnRepeatLoop = False
  13.             End If
  14.             .MoveNext
  15.         Loop
  16.     End With
Feb 25 '09 #6

NeoPa
Expert Mod 15k+
P: 31,418
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCalcEquation(strEquation As String) As Long
  2.  
  3.     Dim MyDB As DAO.Database
  4.  
  5.     Set MyDB = CurrentDb
  6.     With MyDB.OpenRecordset("SELECT * FROM parmas")
  7.         Do Until .EOF
  8.             strEquation = Replace(strEquation, _
  9.                                   "[" & !ParameterShortDesc & "]", _
  10.                                   !Value)
  11.             .MoveNext
  12.         Loop
  13.     End With
  14.  
  15.     Set MyDB = Nothing
  16.  
  17.     fCalcEquation = Eval(strEquation)
  18.  
  19.     'Note: Explanation
  20.     'All parameters found in [parmas] are replaced where found in strEquation with the corresponding [Value].
  21.  
  22. End Function
I hope this is useful.
Feb 25 '09 #7

Expert 100+
P: 1,287
Neo is too quick for me!
Feb 25 '09 #8

NeoPa
Expert Mod 15k+
P: 31,418
I've dealt with similar issues before Chip ;) Good work yourself anyway :)
Feb 25 '09 #9

NeoPa
Expert Mod 15k+
P: 31,418
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.
Feb 25 '09 #10

100+
P: 129
Thanks for all your advise, i have solved it by renaming the Parameters, also i have attached the Replace syntax as well. Thanks again!
Feb 25 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.