2 Replies Latest reply: Mar 26, 2012 1:32 PM by David Space RSS

    Using Dates to filter table box

      I have a data source with several fields including a date field (mm/dd/yyyy).  I want to create a table box that shows the sum of an amount for fieldA vs. FieldB but only for a date range that is entered by the user in variables vStartDate and vEndDate.

       

      I've tried to follow the examples in the community, but the table keeps coming up empty.

       

      The expression I'm using is:

       

      =(Sum({$<PostDate={">$(vStartDate)<=$(vEndDate)"}>} Amount))

       

      So, I would have expected seeing the sum of amount for all records that have a postdate between vstartdate and venddate for fieldA vs FieldB.

       

      vStartDate and VEndDate are defined as numbers of type Date.

       

      Is there anything obvious that I'm doing wrong?  Thanks for any help.

        • Using Dates to filter table box

          Hi Hope I understood ur prblm correctly!!

          vStartDate and vEndDate should be in smae format as of PostDate.

          If Postdate is dd/mm/yyyy then  the two variables should also return the same format date .

          Convert vStartDate and vEndDate into Date format.

           

           

          Hope this helps.

            • Using Dates to filter table box

              This is helpful, thanks.

               

              I changed my expression to:

              =(Sum({$<PostDate={">=$(Date(vStartDate))<=$(Date(vEndDate))"}>} Amount))

               

              and now I'm getting the table filled in, but it's filling in on the basis of all the records - it's ignoring the date constraints.

               

              PostDate is a SQL Server field of type smallDateTime.