Complicated Equation Function Problem

[Constantine AI]

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+(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!

[ChipR]

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

[Stewart Ross]

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]

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]

Good point. The Replace function will still require that no parameter be a subset of another parameter.

[Constantine AI]

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]

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]

Neo is too quick for me!

[NeoPa]

I've dealt with similar issues before Chip ;) Good work yourself anyway :)

[NeoPa]

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.

[Constantine AI]

Thanks for all your advise, i have solved it by renaming the Parameters, also i have attached the Replace syntax as well. Thanks again!