Defining variables and assinging then a value

pomarquis

Forums

Hi everyone!
I'm a beginer in Idea Script and here is a code i ended up with after having recorded a macro :
Sub Main
Set db = Client.OpenDatabase("GLOriginal-Travaillé.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "TYPE_DE_COMPTE"
field.Description = "Ajouter une type de compte pour bâtir TCD"
field.Type = WI_VIRT_CHAR
field.Equation = "@If(@Between( NO_COMPTE;4000;4999 ) ;""Revenus"";(@If(@BETWEEN( NO_COMPTE ;5000;9999);""Dépenses"";(@If( NO_COMPTE =2310 .OR.                    NO_COMPTE  = 2310;""TPS"";(@If( NO_COMPTE =2340 .OR.  NO_COMPTE  = 2340;""TVQ"";(@If( NO_COMPTE =2315 ;""CTI"";(@If( NO_COMPTE =2345 ;""RTI"";""Autre"")))))))))))"
field.Length = 10
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Sub
 
And now, i want to change the numbers highlighted above by using a variable such as Revenusminimalvalue, Revenusmaximalvalue etc etc
I'm trying to define a variable and assign it a value by doing this:
Dim Revenusminimalvalue As Integer
...
Revenusminimalvalue = 4000
so it looks like this :
 
Option Explicit
Sub Main
 
Dim db As Object
Dim task As Object
Dim field As Object
Dim RevMinVal As Integer
 
RevMinVal = 4000
 
Set db = Client.OpenDatabase("GLOriginal-Travaillé.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "TYPE_DE_COMPTE"
field.Description = "Ajouter une type de compte pour bâtir TCD"
field.Type = WI_VIRT_CHAR
field.Equation = "@If(@Between( NO_COMPTE;Revenusminimalvalue;4999 ) ;""Revenus"";(@If(@BETWEEN( NO_COMPTE ;RevMinVal;9999);""Dépenses"";(@If( NO_COMPTE =2310 .OR.  NO_COMPTE  = 2310;""TPS"";(@If( NO_COMPTE =2340 .OR.  NO_COMPTE  = 2340;""TVQ"";(@If( NO_COMPTE =2315 ;""CTI"";(@If( NO_COMPTE =2345 ;""RTI"";""Autre"")))))))))))"
field.Length = 10
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Sub
But when runing the test code, i have an error on the line of code 
 
task.AppendField field
 
and i dont really know why ?
 
Please help !

Brian Element Fri, 02/21/2020 - 07:31

Hi pomarquis,

The problem is that you are using a variable inside of the double quotes of the equation.  If an item is within a double quotes it stays as its what it is, it is not considered a variable.  The easiest way to understand this is to set your equation to a variable and then view the variable such as:

eqn = "@If(@Between( NO_COMPTE;Revenusminimalvalue;4999 ) ;""Revenus"";(@If(@BETWEEN( NO_COMPTE ;RevMinVal;9999);""Dépenses"";(@If( NO_COMPTE =2310 .OR.  NO_COMPTE  = 2310;""TPS"";(@If( NO_COMPTE =2340 .OR.  NO_COMPTE  = 2340;""TVQ"";(@If( NO_COMPTE =2315 ;""CTI"";(@If( NO_COMPTE =2345 ;""RTI"";""Autre"")))))))))))"

msgbox eqn

As you can see your equation is using the names of your variables and not the values.  To fix this change your equation to:

"@If(@Between( NO_COMPTE;" & Revenusminimalvalue & ";4999 ) ;""Revenus"";(@If(@BETWEEN( NO_COMPTE ;" & RevMinVal & ";9999);""Dépenses"";(@If( NO_COMPTE =2310 .OR.  NO_COMPTE  = 2310;""TPS"";(@If( NO_COMPTE =2340 .OR.  NO_COMPTE  = 2340;""TVQ"";(@If( NO_COMPTE =2315 ;""CTI"";(@If( NO_COMPTE =2345 ;""RTI"";""Autre"")))))))))))"

You will notice that I close the double quotes and use the & to insert the variable.

Hopefully this makes sense if not let me know and we can discuss further.

pomarquis Thu, 02/27/2020 - 10:51

Tried it and it worked instantly !
As i said, i have zero background in programming so for me your forum is a goldmine !
Thanks Brian,