Capturing data enterred by user
Forums
I have a script requiring the user to enter a run date
I get the following error when running it - "Error on line 26 - type mismatch" (see file upload)
The coding is below - where am I going wrong? (see file upload)
Hi Russ,
Hi Russ,
Here is the adjusted script. The RunDate needs to be a string variable as it is part of an equation and the equation is a string. You also need to put the date in the format YYYYMMDD as this is how it is stored in the IDEA database. Finally for the equation you need to have a field equation to the variable. I have commented the updated script below.
Brian
Sub Main
Call TestFunc2() ' Test different coding examples
End Sub
Function TestFunc2
' Create the input for Run Date
Dim RunDate As String 'Should be string and not date as the eqn is a string
Dim eqn As String 'Variable to hold the equation
RunDate = InputBox("Enter Run Date MM/DD/YYYY", "Run Date", "")
' Open the database.
Set db = Client.OpenDatabase("Current_Period.IMD")
'need to format the RunDate as YYYYMMDD as this is how IDEA stores it internally
RunDate = iRight(RunDate, 4) & iLeft(RunDate, 2) & iMid(RunDate, 4, 2)
' Display the result.
MsgBox "Run Date: " & RunDate
' Create the task.
Set Mgt = db.TableManagement
' Obtain a reference to the table.
Set ThisTable = db.TableDef
' Create a new field.
Set FirstField = ThisTable.NewField
' Configure a new field.
FirstField.Name = "AsOfDate"
FirstField Description = "As of Date"
FirstField.Type = WI_DATE_FIELD
'need a date to equal the run date
eqn = "INV_DATE == """ & RunDate & """"
FirstField.Equation = eqn
FirstField.Length = 10
' Add the field.
Mgt.AppendField Field
Mgt.PerformTask
Set Mgt = db.TableManagement
End Function
Brian, thank you for your
Brian, thank you for your response it has been helpful but wehn I instituted your code and it still gave me an error. I have been trying different things and still get an invalid equation error or a data type mismatch. When I run the code in the attached script, I get no error. When I modify the equation to "field.Equation = "ASOFDATE == """ & RUNDATE & """"", I get an error that the fields are different data types. Do you have an explanation?
Hi Russ,
Hi Russ,
Sorry I gave you some bad advice. In the script I posted I gave you an extraction equation and not one for a virtual field. So the line instead of eqn = "INV_DATE == """ & RunDate & """" should be eqn = "@Ctod(""" & RunDate & """, ""YYYYMMDD"")"
This will create the virtual date field. If your field ASOFDATE is a date field it should now work. In your second example of the test field you are creating a character field, if the ASOFDATE is a date field that is why you are getting that error as you are trying to compare a character field to a date field, they need to be of the same type.
Hope this help.
Brian
Hi Russ,
Hi Russ,
Ok, figured it out. The main problem was your line Mgt.AppendField had Field instead of FristField. Using the Option Explicit would have found this error right away. I also redid the equation so it saves a few lines.
Thanks
Brian
Sub Main
Call TestFunc2() ' Test different coding examples
End Sub
Function TestFunc2
' Create the input for Run Date
Dim RunDate As String 'Should be string and not date as the eqn is a string
Dim eqn As String 'Variable to hold the equation
RunDate = InputBox("Enter Run Date MM/DD/YYYY", "Run Date", "MM/DD/YYYY")
' Open the database.
Set db = Client.OpenDatabase("Proactive Disclosure-SHEET1.IMD") 'Current_Period
'need to format the RunDate as YYYYMMDD as this is how IDEA stores it internally
' Display the result.
MsgBox "Run Date: " & RunDate
' Create the task.
Set Mgt = db.TableManagement
' Obtain a reference to the table.
Set ThisTable = db.TableDef
' Create a new field.
Set FirstField = ThisTable.NewField
' Configure a new field.
FirstField.Name = "AsOfDate"
FirstField Description = "As of Date"
FirstField.Type = WI_VIRT_DATE
'need a date to equal the run date
eqn = "@Ctod(""" & RunDate & """, ""MM/DD/YYYY"")"
FirstField.Equation = eqn
FirstField.Length = 8
' Add the field.
Mgt.AppendField FirstField 'originally was Field
Mgt.PerformTask
Set Mgt = db.TableManagement
End Function
New Questions
New Questions