Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
A table has around 200.000 records; the columns are date & time and values (a value every 10 minutes).
The result is similar to:
date (mm/dd/yyyy) & time (hh:mm:ss) | value |
---|---|
Name Table.Date | Name Table.Value |
08/31/2011 00.00.00 | 423 |
08/31/2011 00.10.00 | 5287 |
08/31/2011 00.20.00 (and go on...) | 2857 |
09/01/2011 00.00.00 | 36 |
09/01/2011 00.10.00 | 789 |
I need to filter by date, eg from 1st september 2011 to 30th september 2011. Then I have to filter again for time, eg from 00.00.00 to 03.00.00.
I'm trying to implemet a macro, using 4 input fields (dateFrom, dateTo, TimeFrom, TimeTo) and a button to invoke the macro. No any validate data is necessary in this case.
This is my first macro code:
sub [Filter]
rem trying to set value input field into a variable...
set dateFrom = ActiveDocument.Variables("dateFrom")
set dateTo = ActiveDocument.Variables("dateTo")
rem trying to apply the filter, but it seems it doesn't work
ActiveDocument.Fields("Name Table.Date").Select ">$(dateFrom) 00.00.00"
ActiveDocument.Fields("Name Table.Date").ToggleSelect ">$(dateTo) 11.00.00"
rem trying to apply the filter, it seems it works
ActiveDocument.Fields("Name Table.Date").Select ">09/01/2011 00.00.00"
ActiveDocument.Fields("Name Table.Date").ToggleSelect ">09/01/2011 11.00.00"
end sub
How can I implement this filter? How can I fix this macro?
Just ask questions for further infos...
Thanks for helping
Hi,
instead of doing this:
set dateFrom = ActiveDocument.Variables("dateFrom")
set dateTo = ActiveDocument.Variables("dateTo")
ActiveDocument.Fields("Name Table.Date").Select ">$(dateFrom) 00.00.00"
ActiveDocument.Fields("Name Table.Date").ToggleSelect ">$(dateTo) 11.00.00"
Try this:
dateFrom = ActiveDocument.Variables("dateFrom").GetContent.String
dateTo = ActiveDocument.Variables("dateTo").GetContent.String
ActiveDocument.Fields("Name Table.Date").Select ">" & dateFrom & " 00.00.00"
ActiveDocument.Fields("Name Table.Date").ToggleSelect ">" & dateTo & " 11.00.00"
* In VB Script, you cannot use DollarSignExpansion.
Hope this helps you.
Regards,
Fernando
Hi,
instead of doing this:
set dateFrom = ActiveDocument.Variables("dateFrom")
set dateTo = ActiveDocument.Variables("dateTo")
ActiveDocument.Fields("Name Table.Date").Select ">$(dateFrom) 00.00.00"
ActiveDocument.Fields("Name Table.Date").ToggleSelect ">$(dateTo) 11.00.00"
Try this:
dateFrom = ActiveDocument.Variables("dateFrom").GetContent.String
dateTo = ActiveDocument.Variables("dateTo").GetContent.String
ActiveDocument.Fields("Name Table.Date").Select ">" & dateFrom & " 00.00.00"
ActiveDocument.Fields("Name Table.Date").ToggleSelect ">" & dateTo & " 11.00.00"
* In VB Script, you cannot use DollarSignExpansion.
Hope this helps you.
Regards,
Fernando
Thank you for your help.
I was able to develop the little macro from your example and added another filter by time the same system
My table has two columns, one for date and another one for time; macro will run first select (from date to date) and will run a second select on first result set (from time to time).