2 Replies Latest reply on Jun 23, 2011 12:31 PM by BrettTrusko

    Date range selection



      Date range selection


      I have a date field in the format xx/xx/xxxx. I am trying to calculate how many entries were done in a month over a period of time. For example, creation_date 12/11/10. When i create calculation fields with the following calculations:

      CREATED_DATE  ≥  "12/1/2010"and CREATED_DATE ≤ "12/31/2010"

      I get a result (1 of course)

      When I create the the following:

      CREATED_DATE  ≥  "12/1/2011"and CREATED_DATE ≤ "12/31/2011"

      I also get a positive result.

      Obviously, I am doing this only 1/2 right since I get a positive result.

        • 1. Re: Date range selection

          Assuming CREATED_DATE is a field of type date, you have a data type problem. "12/1/2010" is interpreted by FileMaker as text, not the single integer that is actually stored as a "date" in a date field.

          CREATED_DATE  ≥  GetasDate ( "12/1/2010" ) and CREATED_DATE ≤ GetasDate ( "12/31/2010" )

          is one option.

          CREATED_DATE  ≥  Date ( 12 ; 1 ; 2010 ) and CREATED_DATE ≤ Date ( 12 ; 31 ; 2010 )

          is another.

          There are also other calculation that can be used to identify all records with a date that falls in a given month as well as several different find requests you can use to find dates for given to find all records of a given month.

          • 2. Re: Date range selection

            This worked perfectly! I also learned something new. Thank you.