5 Replies Latest reply on May 25, 2012 1:19 PM by philmodjunk

    selecting a date range?



      selecting a date range?


      I have a layout that includes a date field.  I would like to be able to select and have it identify, for example, all records in the date range of 6/1/12 ...6/30/12.  Or any other selected range.  What is the easiest way to do this?  Thanks

        • 1. Re: selecting a date range?

          How do you want to display the results? Do you want to perform a find for records or display data in a portal? Both approaches can use a date range.

          Here's the basic principle for performing a find using date range criteria:

          enter find mode

          Enter your date range exactly in the format you've shown above and perform the find.

          You can also enter 6/2012 as your find criteria and find all records for June, 2012.

          You can use this method in a script to find records in a date range:

          Define two global date fields, gDate1, gDate2 in your database and put them on your layout.

          Your script would look like this:

          Enter Find Mode []
          Set field [YourTable::YourDateField ; YourTable::gDate1 & "..." & Yourtable::gDate2 ]
          Set error capture [on]
          Perform Find[]

          This script assumes that you are already on the layout you intend to use to disply the results of your find. The script will fail if gDate1 and gDate2 do not have global storage specified in field options.

          • 2. Re: selecting a date range?

            I was looking to do this too for a Layout that is a Line Graph Chart and your answer worked great!  I created the script above and added it to a button.

            I then made a this script and added that to a second button to clear the results and reset the records to default view:

            Clear [Select;YourTable::gDate1]
            Clear [Select;YourTable::gDate2]
            Show All Records

            • 3. Re: selecting a date range?

              I clear fields by using:

              Set Field [YourTable::gDate1 ; ""]

              Like copy, paste and the steps that start with insert, clear only works if the field is present on the current layout--something that may not be the case with such search fields as you often use one layout (perhaps in a small window) to specify the search criteria and a second layout to display the results.

              • 4. Re: selecting a date range?

                I've got my search fields on my chart, but it's good to know reasons as to use a different syntax for different situations.

                It still requires the Show All Records to reset to the full dataset in the chart, correct?  Simply setting gDate1/2 to "" doesn't reset the chart.

                • 5. Re: selecting a date range?

                  You are correct.

                  One of the other reasons I make it a basic rule to avoid using the script steps I listed is that future changes to the layout's design can cause them to fail and there's no obvious error message that appears to tell you why it failed. Thus, I use set field and avoid possible future trouble.