3 Replies Latest reply on Jul 18, 2012 6:37 AM by philmodjunk

    How to calculate No of Records with no date

    HugoLidia

      Title

      How to calculate No of Records with no date

      Post

      We have a 10 records that have a "DateReceived" field and a "DateCollected" field, we want to be able to display a count of the records where the "DateCollected" field is empty.

      Does not calculate correctly if using a statement like:

      If(DateCollected <> " ", result1, result2) or  If(DateCollected > 0, result1, result2)

        • 1. Re: How to calculate No of Records with no date
          philmodjunk

          And where (in what table) did you define the calculation?

          Define it as

          IsEmpty ( dateCollected )

          clear the do not evaluate if all referenced fields are empty check box.

          Define this in the same table as dateCollected.

          Then use a count of Summary field in the same table or the count function from a related table to count the records.

          • 2. Re: How to calculate No of Records with no date
            HugoLidia

             Thanks for input PhilModJunk.

            There is one table - Gifts. 

            We have a calculation field with : "Uncollected Gifts : " & IsEmpty(DateCollected)

            Which returns : "Uncollected Gifts : 1"

            I had expected this to return 9, if only 1 of the 10 recirds has a date entered in it.

             

            "Uncollected Gifts : " & NOT IsEmpty(DateCollected) on the otherhand Returns : 0

            Am I missing a logical step here?

            • 3. Re: How to calculate No of Records with no date
              philmodjunk

              why would it do that as it only references a single record in your table?

              As I have already posted, add  a summary field set up as the total of Uncollected Gifts and it will return 9 if the 10 records you describe make up your current found set. If you need the total for all records in your table, you'll need to set up a relationship for this.