Skip to main content

compare dates

My name is Lucas and I'm from Argentina, I really like your blog.
 
I have a query, I'm doing a "macro" for IDEA with visual basic, where from a base I take a field that has a date in string format I convert it to date. Then I want the user to enter a date with which to compare that data previously obtained, in order to have the base already filtered.
 
Sorry for my english.
 
I have made the following script:
 
' File - Import Assistant: Delimited Text
 
Function Test
dbName = "E070.IMD"
Client.ImportDelimFile "C:\Users\ldesimone\Desktop\Prueba\E070.txt", dbName, FALSE, "", "C:\Users\ldesimone\Desktop\Macro IDEA\Import Definitions.ILB\E070.RDF", TRUE
Client.OpenDatabase (dbName)
' Append Field
' Creamos una nueva columna fecha, virtual date para poder trabajar sobre ella.
Set db = Client.OpenDatabase("E070.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "FECHA"
field.Description = ""
field.Type = WI_VIRT_DATE
field.Equation = "@Ctod(AS4DATE; ""DD/MM/YYYY"")"
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
 
' Data: Direct Extraction
Function DirectExtraction
'Aplicamos los filtros de tiempo para obtener el universo.
Dim fecha_inic As Date
Dim fecha_fin As Date
fecha_inic = InputBox("Ingrese fecha inicial")
fecha_fin = InputBox ("Ingrese fecha final")
Set db = Client.OpenDatabase("E070.IMD")
Set task = db.Extraction
task.IncludeAllFields
dbName = "UNIVERSO DE CAMBIOS.IMD"
'cant_reg = db.count
task.AddExtraction dbName, "", "AS4DATE>= ""fec_inic"" .AND. AS4DATE <= ""fec_final"""
task.CreateVirtualDatabase = False
task.PerformTask 1, db.Count
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
But at the time of compiling I get an error in task.PerformTask 1, db.Count, I read in another entry of your page that can be with the syntax. You could help me with my scrip, of course, thank you very much.

Brian Element Fri, 05/24/2019 - 13:54

Hi Lucas, I am glad you are enjoying the site.

The first problem I see is with the following line:

task.AddExtraction dbName, "", "AS4DATE>= ""fec_inic"" .AND. AS4DATE <= ""fec_final"""

You need to use the ampersand (&) when combining a variable obtained through the script and adding it to you equation.  So the line should change to the following:

task.AddExtraction dbName, "", "AS4DATE>= """ & fec_inic & """ .AND. AS4DATE <= """ & fec_final & """"

I added on an additional double quote and ampersand in order to attach the two variables to the equation.  

I also notice that you created the field FECHA as a date field for AS4DATE so should the above equation use FECHA instead of AS4DATE?  One last thing, when the user enters in the date through the input it has to be in the YYYYMMDD format, you might also have to change your equation to add a @CtoD() function to transform the input to a date format but you might not, you will see if IDEA gives you an error.

Hope this helps out.

Brian