Using a cell value as a parameter in another table extraction

jan.waj

Forums

Hey guys,

Im trying to extract values from a table with the criteria being that the values in the columns have to be >= 30. This parameter, however, can vary. I created a separate Excel-table containing this value to ensure that other people dont have to go into the code and change it there.

Hence, my aim is to save this value in a variable and then use it in the extraction as the mentioned criteria.
---------------------------------------------------------------------------------------------------
My macro looks like this:

' Open the database.
Set db = Client.OpenDatabase("Parameter_Datum-Tabelle1.IMD")

' Obtain the RecordSet from the database.
Set rs = db.RecordSet

' Obtain the first record from the RecordSet.
rs.GetAt(1)
Set rec = rs.ActiveRecord

' Show that the record holds data.
MsgBox "Datumsdifferenz = " & rec.GetNumValue("DATUMSDIFFERENZ")
'___________________________

'Extraction:
Set db = Client.OpenDatabase("benutzte Kreditkarten.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "Datumsabweichungen.IMD"
task.AddExtraction dbName, "", "@age(BLDAT; CPUDAT) >=" & rec.GetNumValue("DATUMSDIFFERENZ") & ".OR. @age(CPUDAT;BUDAT) >=" & rec.GetNumValue("DATUMSDIFFERENZ")
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing

Client.CloseDatabase "benutzte Kreditkarten.IMD"
'__________________________

However, it returns at task.PerformTask 1, db.Count that the equation is incorrect.

Does anybody perhaps have an idea?

Hopefully I was able to explain the problem properly.

Many thanks in advance,

cheers,
Jan

Brian Element Thu, 10/15/2020 - 12:10
Hi Jan, It is probably because you are using the rec.GetNumValue in the addextraction function. Sometimes IDEA doesn't like it when you nest certain things. Try making the two GetNumValues equal to a variable and use the variable on the line. Hopefully that will fix the problem. Let me know if that works, if not I will look at it again but that is the first thing I would try. Brian

jan.waj Fri, 10/16/2020 - 04:32

Hey, Brian,

thank you very much for that response. I tried it, once I read your advice, however, it still gives the same error message (sth like "incorrect equation was set") at the same place in the macro.
Now my macro looks like the following:


'Getting the parameter from the designated table:

Dim datedif As Integer

' Open the database.
Set db = Client.OpenDatabase("Parameter_Datum-Tabelle1.IMD")

' Obtain the RecordSet from the database.
Set rs = db.RecordSet

' Obtain the first record from the RecordSet.
rs.GetAt(1)
Set rec = rs.ActiveRecord

' Show that the record holds data.
MsgBox "Datumsdifferenz = " & rec.GetNumValue("DATUMSDIFFERENZ")

datedif = rec.GetNumValue("DATUMSDIFFERENZ")

'____________________________________________________________________________________
'Extraction:

Set db = Client.OpenDatabase("benutzte Kreditkarten.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "Datumsabweichungen.IMD"
task.AddExtraction dbName, "", "@age(BLDAT; CPUDAT) >=" & datedif & ".OR. @age(CPUDAT;BUDAT) >=" & datedif
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing

Client.CloseDatabase "benutzte Kreditkarten.IMD"

Perhaps the problem is somewhere else..

Many thanks, Brian!

Cheers,
Jan

Brian Element Fri, 10/16/2020 - 06:08
Hi Jan, thanks for trying that out. The next thing I would try is seeing what the equation looks like so before the task.AddExtraction I would add the following line: msgbox "@age(BLDAT; CPUDAT) >=" & datedif & ".OR. @age(CPUDAT;BUDAT) >=" & datedif and then review it to see if it looks ok and if it does try it directly in IDEA in the equation editor and see if it gives an error.

jan.waj Fri, 10/16/2020 - 09:17

Hello Brian,

so. It works now. Honestly, though? I dont know how. I think the problem was more with the syntax of the Age-function: I changed a couple of things within the Age-function. At first, it didnt work, but now it does. It certainly wasnt a problem with the variable.

This is how the code looks now:

Dim datedif As Integer

' Open the database.
Set db = Client.OpenDatabase("Parameter_Datum-Tabelle1.IMD")

' Obtain the RecordSet from the database.
Set rs = db.RecordSet

' Obtain the first record from the RecordSet.
rs.GetAt(1)
Set rec = rs.ActiveRecord

' Show that the record holds data.
MsgBox "Datumsdifferenz = " & rec.GetNumValue("DATUMSDIFFERENZ")

datedif = rec.GetNumValue("DATUMSDIFFERENZ")

' Clear the memory.
'Set db = Nothing
'Set rs = Nothing
'Set rec = Nothing

'Client.CloseDatabase("Parameter_Datum-Tabelle1.IMD")

'---------------------------------------------------------------------------------

'Datumsunterschiede werden herausgefiltert per Kriterium in Tabelle "Parameter_Datum-Tabelle1":

'Extraktion mit Kriterium:

Set db = Client.OpenDatabase("benutzte Kreditkarten.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "Datumsabweichungen.IMD"
task.AddExtraction dbName, "", "@Age(BUDAT; BLDAT) >="& datedif &" .OR. @Age(CPUDT; BLDAT) >="& datedif
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing

Client.CloseDatabase "benutzte Kreditkarten.IMD"

Thank you very much, Brian.
Till next time! haha

Cheers,

Jan