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.

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 strParameter = !ParameterShortDesc 

strValue = !Value

Replace(strEquation, strParameter, strValue)

.MoveNext

Stewart Ross Inverness

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 :

Expand|Select|Wrap|Line Numbers

Equation = Height-PlinthH-FrameBH-FrameTH-DoorBH-DoorTH+(DoorPanelTen*2)

Should really read :

Expand|Select|Wrap|Line Numbers

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

 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?

Expand|Select|Wrap|Line Numbers

     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.

Expand|Select|Wrap|Line Numbers

 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!

Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

re: Complicated Equation Function Problem

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about