Expression in Equation parameter of the append field function
Forums
Dear all,
I have a problem of passion gthe correct argument wihtin the string associated to my equation expression of append field function. I have created bespoke append field function (below):
' Appending field
Function AddFieldToDatabase(TargetDb As Database,NewField_Name As String,Req_Field_Type As Integer ,Criteria As String)
'Set TargetDb = MasterDb this is not always MasterDb
'Creating the task
Set task = TargetDb.TableManagement
'Obtaining reference to the table
Set TargetField = TargetDb.TableDef.NewField
'Specify the name of the new field
TargetField.Name = NewField_Name
On Error GoTo FieldDataTypeError '(This error will occur if the resulting type of the field created by the code is different than requested one in the function Call (indicated by the number 1-4))
If Req_Field_Type = 1 Then
'Get the virtual Date field
TargetField.Type = WI_VIRT_DATE
TargetField.Length = 8
ElseIf Req_Field_Type = 2 Then
'Get the virtual Character field
TargetField.Type = WI_VIRT_CHAR
TargetField.Length = 20
ElseIf Req_Field_Type = 3 Then
'Get the virtual Numeric field
TargetField.Type = WI_VIRT_NUM
TargetField.Length = 10
TargetField.Decimals = 0
ElseIf Req_Field_Type = 4 Then
'Get the virtual Time field
TargetField.Type = WI_VIRT_TIME
TargetField.Length = 8
End If
'Set the criteria (equation) and perform the task
TargetField.Equation = Criteria
task.AppendField TargetField
task.PerformTask
GoTo CleanFunctionSection
FieldDataTypeError:
MsgBox "Field Type for the new field (column) generated by the equation is different than requested within AddFieldToDatabase function."
Exit Sub
CleanFunctionSection:
Set task = Nothing
Set TargetField = Nothing
Set TargetDb = Nothing
End Function
At some point in the programme I call my append field function from the below function:
Function Append_Field_Sales_JE_Tag(MasterDb As Object,Account As String)
Dim DatabaseName As String
Dim DatabaseField As String
Dim FieldInput As String
DatabaseName = Account & " journals.IMD"
DatabaseField = Account & "_JE_TAG"
FieldInput = Account & " JE"
MsgBox DatabaseName
MsgBox DatabaseField
MsgBox FieldInput
Set TargetDb = Client.OpenDatabase(Account & " journals.IMD")
Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput)
End Function
However i experience a huge problem to pass correctly FieldInput element. Account string within
FieldInput is essentially string from one of the field in the database and is declared in earlier stage of my programme.The result of FieldInput is string 'Revenue JE".
I need to highlight that the message in Msgbox associated to FieldInput variable is displaying correct string of "Revenue JE".
The only way the line:
Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput)
works is when I replace the FieldInput with the hard coded """Revenue JE""" however thats not the point as my Account variable will change thoughout the programme and will get different value on the second run (execution) of the programme.
Morning Brian,
Morning Brian,
No, the Account variable is correct and the FieldInput is correct as well.
FieldInput = Account & " JE" displays the correct string.
The problem starts when I input that variable into my call function:
Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput)
Then it is not recognized and throws an error. My generic functiion for the append field function is given in my first post on this topic.
DatabaseField variable is
DatabaseField variable is not causing any issue.It working in my call function for either expression: Account & " _JE_TAG" or DatabaseField.
The issue is caused by the FieldInput variable which is essentially Criteria element in my function which holds the actual equation for the append field.
I just noticed something,
I just noticed something, your call is the following:
Call AddFieldToDatabase_Character(TargetDb,Account & " _JE_TAG",FieldInput)
You are sending 3 variables to the AddFieldToDatabase_Character but your AddFieldToDataase function has four parameters and the thrid is an integer but in your call you are sending a string. I know the function name is different but do you want to make sure that your call has the same number of parameters and same time as the function it is calling.
AddFieldToDatabase(TargetDb As Database,NewField_Name As String,Req_Field_Type As Integer ,Criteria As String)
Yes , i have modified my
Yes , i have modified my original bespoke functiion to create only 1 type of field - character one. Hence there is a call to Function AddFieldToDatabase_Character (which is given below).
Function AddFieldToDatabase_Character(TargetDb As Database,NewField_Name As String,Criteria As String)
'Set TargetDb = MasterDb this is not always MasterDb
'Creating the task
Set task = TargetDb.TableManagement
'Obtaining reference to the table
Set TargetField = TargetDb.TableDef.NewField
'Specify the name of the new field
TargetField.Name = NewField_Name
'Get the virtual Character field
TargetField.Type = WI_VIRT_CHAR
TargetField.Length = 20
'Set the criteria (equation) and perform the task
TargetField.Equation = Criteria
task.AppendField TargetField
task.PerformTask
Set task = Nothing
Set TargetField = Nothing
Set TargetDb = Nothing
End Function
That function has only 3 arguments as it is supposed to create only virtual character field type and therefore doesnt need the type argument specified by integer 1 to 4.
Therefore as you can see it doesnt include Req_Field_Type As Integer argument.
Apologies for confusion but having said that the issue is still around the last argument in the function which is Criteria associated to the field.Equation line.
The error message is not very
The error message is not very helpful as it only says:"Syntax error" and provides the line number of the code where the error occurs.That line indicates the problem is around task.AppendField TargetField exection (performing task) of my generic function.
I think the issue is around passing the argument from the dialog box into the function argument and then passing that argument into my generic append field function.
Ok, I think I have it. The
Ok, I think I have it. The problem is not with your IDEAScript but with your criteria. If you get the syntax error it means that your IDEA equation is not proper and won't run. So I would suggest just before getting to that line and using a message box to see what your criteria is and try it directly in the equation editor to figure out what is wrong with it.
So is the Account variable
So is the Account variable blank or not holding what you expect? I don't have enough of the code to see what the problem is, you are defining Account somewhere so you need to trace it back to see where it is changed.