Searching dates with direct extraction with a variable

JoelTay

Forums

Hi, 
I am relatively new with IDEAscript, but faced an issue that I finds difficult to resolve even with searching for help on the web. 
My script is meant to recieve input from a database, using the value and change it into criteria to filter the result,
I was constantly prompted with bad equation, I have also changed to array but same result. 
here is a snip shot of my code (highlighted yellow and bold as the issue):
 
Call multidatesearch("20200403,20200503,20200305","Sample")
'Extract result based on a date provided by input
Function multidatesearch(parameter, filename)
Dim doom As String
' Creating the criteria
For i = 1 To Len(parameter)
If i = 1 Then
doom  = doom + post_date + " = " + """" + CStr(Mid(parameter,i,1))
ElseIf Mid(parameter,i,1) = " " Then
                       Nothing
ElseIf Mid(parameter,i,1) = "," Then
doom  =  doom + """"
doom = doom + " .OR. " +post_date + "=" + """"
ElseIf i = Len(parameter) Then
doom  =  doom + """"
Else
doom = doom + CStr(Mid(parameter,i,1))
End If
 
Next i
 
MsgBox doom 
 'Direct extract result
Set db = Client.OpenDatabase(source)
Set task = db.Extraction
task.IncludeAllFields
dbName = filename+".IMD"
task.AddExtraction dbName, "",  doom
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
End Function

Brian Element Wed, 01/20/2021 - 10:55

Hi Joel,

A few things you need to change when creating the criteria.  The nothing in the first elseif statement has to be removed as it will give a syntax error as it is not needed.  You can leave if statements, functions blank in IDEAScript.  The second item is the post_date I assume is your field name?  If so it needs to be within quotes (""), also you should capitalize it as IDEA will capitalize it for you and sometimes you might not get what you expect.  Here I update that section of your script for you:


For i = 1 To Len(parameter)
		If i = 1 Then
			doom  = doom + "POST_DATE" + " = " + """" + CStr(Mid(parameter,i,1))
		ElseIf Mid(parameter,i,1) = " " Then
		                       
		ElseIf Mid(parameter,i,1) = "," Then
			doom  =  doom + """"
			doom = doom + " .OR. " + "POST_DATE" + "=" + """"
		ElseIf i = Len(parameter) Then
			doom  =  doom + """"
		Else
			doom = doom + CStr(Mid(parameter,i,1))
		End If
	 
	Next i

JoelTay Wed, 01/20/2021 - 21:33

Hi Brian,
Sorry about the incomplete script, I realise I did not provide my global variable. I have readadjusted my script as a single Ideascript. I have tried your script, but may not have used it correctly. Can I ask for your help over the script again.
Sub Main
source = "General Ledger.IMD"
Dim doom As String
parameter = "20200105,20200405"
filename = "Hello"
postdate = "POSTING_DATE"
 
For i = 1 To Len(parameter)
If i = 1 Then
doom  = doom + "POSTING_DATE" + " = " + """" + CStr(Mid(parameter,i,1))
ElseIf Mid(parameter,i,1) = " " Then
                       
ElseIf Mid(parameter,i,1) = "," Then
doom  =  doom + """"
doom = doom + " .OR. " + "POSTING_DATE" + "=" + """"
ElseIf i = Len(parameter) Then
doom  =  doom + """"
Else
doom = doom + CStr(Mid(parameter,i,1))
End If
 
Next i
 
MsgBox doom 
 
Set db = Client.OpenDatabase(source)
Set task = db.Extraction
task.IncludeAllFields
dbName = filename+".IMD"
task.AddExtraction dbName, "",  doom
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
End Sub
 
 

Brian Element Thu, 01/21/2021 - 09:59

Hi Joel,

Here you go, this seems to be working.  Not sure what the problem is but I made a few changes, I used the option explicit so all variables need to be defined.  I also changed the + with the & for concatenation as I found IDEAScript can get confused sometime so I always use the &.  The the quotes I used chr(34) as having """" can get confusing and the code doesn't always read right with that.  Here is my update:


Option Explicit

Sub Main
	Dim doom As String
	Dim source As String
	Dim parameter As String
	Dim filename As String
	Dim postdate As String
	Dim db As database
	Dim task As task
	Dim dbName As String
	Dim i As Integer
	
	source = "General Ledger.IMD"
	parameter = "20200105,20200405"
	filename = "Hello"
	postdate = "POSTING_DATE"
	
	For i = 1 To Len(parameter)
		If i = 1 Then
			doom = postdate & " == " & Chr(34) & Mid(parameter,i,1)

		ElseIf Mid(parameter, i, 1) = " " Then
			'ignore spaces
		ElseIf Mid(parameter, i, 1) = "," Then
			'end of an item or the string so close the quote
			doom = doom & Chr(34) & " .OR. " & postdate & " == " & Chr(34)
		ElseIf i = Len(parameter) Then
			doom = doom & Mid(parameter,i,1) & Chr(34)
		Else
			doom = doom & Mid(parameter,i,1)
		End If
	Next i
	 
	MsgBox doom 
	
	Set db = Client.OpenDatabase(source)
	Set task = db.Extraction
	task.IncludeAllFields
	dbName = filename & ".IMD"
	task.AddExtraction dbName, "", doom 
	task.PerformTask 1, db.Count
	Set task = Nothing
	Set db = Nothing
	Client.OpenDatabase (dbName)
	
End Sub

JoelTay Sat, 01/23/2021 - 00:58

Thank you so much!!!! you have managed to resolved the problem of (") with char(34) that had plagued me for weeks, which I had to manually extract. Thank you! you are a great help!