Capturing data enterred by user

New Questions

Version 2

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 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,

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,

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

Thanks Brian, I have it working fine now