Skip to main content

Inclusion of user inputs in criteria for extraction

Dear all,
I would please like to know how we can leverage user inputs in criteria for performing direct extraction. Currently, I am doing like this :
Getting input from the user
---------
Dim exemplevariable1 As String
exemplevariable1 = InputBox("abc","def")
---------
Perform extraction based on input
Set db = Client.OpenDatabase("input.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "output.IMD" 
task.AddExtraction dbName, "", " exemplecriteria  == ""& exemplevariable1 &"""
task.PerformTask 1, db.Count
---
However the line before the last (task.AddExtraction dbName, "", " exemplecriteria  == ""& exemplevariable1 &""") makes problems either "invalid equation", or "invalid syntax". How do you please proceed to insert the variable in the equation ? Using inputs is important to make script flexible and not have hard coded values.
Thanks a lot for your help
Best regards,
Sami
 

Brian Element Mon, 08/02/2021 - 10:59

Hi Sami,

There are a few ways to do this, it also depends on if the field is a numeric field or not.  If it is a numeric you don't want the value to be encapsulated by quotes but if it is a character, date or time it needs to be encapsulated by quotes.  Here is an example using your code that is for both numeric and character fields.  Also for the character fields I gave an alternative that uses the chr(34) which return the quote.  Hopefully this will make things clear.

code>
Sub Main

	'to perform an extraction on a character, date or time field field
	Dim exemplevariable1 As String

	Set db = Client.OpenDatabase("input.IMD")
	Set task = db.Extraction
	task.IncludeAllFields
	dbName = "output.IMD" 
	task.AddExtraction dbName, "", " CHARACTER_FIELD  == """ & exemplevariable1 & """"
	'alternative way to write this
	'task.AddExtraction dbName, "", " CHARACTER_FIELD  == " & Chr(34) & exemplevariable1 & Chr(34)
	task.PerformTask 1, db.Count
	Set task = Nothing
	Set db = Nothing
	
	'to perform an extraction on a numeric field
	exemplevariable1 = InputBox("Enter a Numeric Amount","Enter a Numeric Amount", "")
	Set db = Client.OpenDatabase("input.IMD")
	Set task = db.Extraction
	task.IncludeAllFields
	dbName = "output.IMD" 
	task.AddExtraction dbName, "", " NUMERIC_FIELD  == " & exemplevariable1
	task.PerformTask 1, db.Count
	Set task = Nothing
	Set db = Nothing
	
End Sub