Skip to main content

Capturing data enterred by user

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)

Brian Element Sun, 01/29/2017 - 11:59

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

 

Russ Thu, 02/02/2017 - 08:51

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? 
 
 
 
 

Brian Element Fri, 02/03/2017 - 07:48

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

Brian Element Fri, 02/03/2017 - 08:18

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