3 Replies Latest reply on Jan 28, 2009 3:45 AM by abitech

    How do I find Records with Dates This or Last or next Month in FileMaker Pro 10?

    abitech

      Title

      How do I find Records with Dates This or Last or next Month in FileMaker Pro 10?

      Post

       

        • 1. Re: How do I find Records with Dates This or Last or next Month in FileMaker Pro 10?
          TSGal

          abitech:

           

          Thank you for your post.

           

          When searching for a specific date range (for example, this month), you could enter Find Mode and enter:

           

          1/1/2009..1/31/2009

           

          This will find all records in the range 1/1/2009 to 1/31/2009.

           

          Since you are finding for an entire month, this can be simplified to:

           

          1/2009

           

          Now, we can put this into a script to make this automatic, so you don't have to enter the month.  That is,

           

          Enter Find Mode []

          Set Field [ <date field> ; "1/2009" ]

          Perform Find []

           

          However, this is static and will not change when we enter February.  Therefore, we can store the month number to a variable and then use the variable to find.  Therefore, the script to find records for this month would be:

           

          Set Variable [ $var ; Month (Get (CurrentDate)) & "/" & Year (Get (CurrentDate)) ]

          Enter Find Mode []

          Set Field [ <date field> ; $var ]

          Perform Find []

           

          ------------

           

          Finding the previous month and next month can be a little tricky.  For the next month, it would seem logical to add 1 (one) to the month, but if you are in December and add one, you get 13, which will not work.  Likewise, for the previous month, subtracting one from January leaves you at 0.

           

          Here is the script for next month, and I'll discuss the calculation afterwards.

           

           

          Set Variable [ $var ; (Mod (Month (Get (CurrentDate)); 12) + 1) & "/" & (Year (Get (CurrentDate)) +

                  If (Month (Get (CurrentDate)) = 12; 1; 0 )) ]

           

          Enter Find Mode []

          Set Field [ <date field> ; $var ]

          Perform Find []

           
          ---------
           
          The Mod() function returns the remainder when dividing by a number.  If we divide by any month by 12, other than December, the remainder is the month number.  For December, dividing 12 by 12 leaves zero remainder.  After that result, we add 1, which would brings us to January if the current month is December.
           
          The previous month can be a little more tricky, but here is a calculation that does work.  It formats the field with the range, for example:
           
          1/1/2009..1/31/2009 
           

          Set Variable [ $var ; Date (Month (Get (CurrentDate)) - 1; 1; Year (Get (CurrentDate) ) ) & "..." &

               Date (Month (Get (CurrentDate) ); 1; Year (Get (CurrentDate) ) ) - 1) ]

          Enter Find Mode []

          Set Field [ <date field> ; $var ]

          Perform Find []


          ---------
           
          Even though the first part of the calculation for this month returns 0/1/2009, FileMaker knows to translate this to 12/1/2008.  For the second half, we find the first day of the month and subtract one day.  This way, we don't have to think about how many days occur in a specific month.  FileMaker takes care of this for us automatically.
           
          I hope these three examples give you some ideas for future calculations.
           
          If you need clarification for any of the above steps, please let me know.
           
          TSGal
          FileMaker, Inc.
          • 2. Re: How do I find Records with Dates This or Last or next Month in FileMaker Pro 10?
            abitech
              

            Thank you, but unfortunaltely none of this works.

            It will only show records for January with "**/01/2009", while it doesn't work with "1/2009".

            The script doesn't work, could it be because I have a different language version of the software or does the language only apply to the layout (menus, etc.?)

            • 3. Re: How do I find Records with Dates This or Last or next Month in FileMaker Pro 10?
              abitech
                

              Ehm...I've realized just now that the date field I'm trying to search wasn't formatted as a date, but as text. Now I've changed it, and I figured out how the script instructions translate in my language, and it works perfectly. Just what I needed for my reports. Thank you!