1 Reply Latest reply on May 17, 2013 3:48 PM by philmodjunk

    searching dates

    JohnPink

      Title

      searching dates

      Post

           Hi

           I am currently trying to setup a database that can search dates, but when i search a date i want it to to be able to bring up not just the partcular date you have set. Ideally i would be able to search a date and it would come up with everything in that quarter or month that it is reerlated too;

           I.e if i search for 1/1/2012

           it would come up with all records that are in January (first month)

           or another script would open a page that would come up with all the related records in that quarter I.e all records that are in Quarter 1 of 2012

           If anyone knows how to do this would be much appricitaed

           Thanks for taking a look

           Pinky

        • 1. Re: searching dates
          philmodjunk

               First, take a look at this thread and the scripted find examples in it:Scripted Find Examples

               It shows a number of ways to use dates to perform a find.

               Here's a set field step to use in such a script that will find all records dated in the same month as the date entered/selected in a global date field:

               Set Field [YourTable::YourDateField ; Month ( Globals::gSearchDate ) & "/" & Year ( Globals::gSearchDate ) ]

               with the date example from your post, this enters: 1/2012 as the search criteria and should find all records dated in the Month of January, 2012.

               Assuming that the first month of the first quarter of your fiscal year is January, the following calculation could be used with Set FIeld to enter a date range for finding all records of the same quarter:

               Let ( [ D = Globals::gSearchDate ;
                         Y = Year ( D ) ;
                         Q = Ceiling ( Month ( D ) / 4 ) ; //returns a value of 1, 2 ,3 or 4 for the quarter
                         M1 = Q * 3 - 2 ;
                         M2 = M1 + 3
                        ] ;
                        Date ( M1 ; 1 ; Y ) & "..." & Date ( M2 ; 0 ; Y )
                       )

               For your example date, this will produce the criteria: 1/1/2012...3/31/2012

               Note that using 0 for the day parameter in the Date function returns the date for the last day of the preceding month.