2 Replies Latest reply: Dec 2, 2011 6:27 AM by present02 RSS

    How to filter values by date and time

      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.DateName Table.Value
      08/31/2011 00.00.00423
      08/31/2011 00.10.005287
      08/31/2011 00.20.00 (and go on...)2857
      09/01/2011 00.00.0036
      09/01/2011 00.10.00789

       

      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

        • How to filter values by date and time
          Fernando Suzuki

          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