6 Replies Latest reply on May 19, 2009 8:51 PM by mayor

    Retrieve data by date

    mayor

      Title

      Retrieve data by date

      Post

      Hello,

       

      I facing a problem how to search the data by month anda year.

      I got 2 tables, research anda expenses

       

      Research ( research_account_no, researcher_name, amount ....)

      Expenses (research_account_no, invoice_no, invoice_date ....)

       

      Relational field : research_account_no

       

      I have created 1 layout based on invoice_date, but how to retrieve data by monthly and yearly, based on invoice_date.

      How to do that? Please advice me.

       

      TQ

        • 1. Re: Retrieve data by date
          mrvodka
            

          Create a calc caled c_report_date:

          Date ( Month (invoice_date); 1; Year (invoice_date) )

           

          Now you can just search in this field for the month and year combo that you want such as 5/1/2009.

           

          Also keep in mind that you could manually search for a date range itself in the invoice_date field.

          Enter Find Mode []

          Set Field [invoice_date; "1/1/2009...12/31/2009"]

          Perform Find []

           

           

           

           

          • 2. Re: Retrieve data by date
            mayor
              

            I have created 1 calculation field called c_report_date and

            put the Date ( Month (invoice_date); 1; Year (invoice_date) )

             

            When I try searching by month (Mei or 5) and year (2009), it cannot

            find the record. FYI, I want to search by month (Mei or 5) instead of

            5/1/2009 as well as year.

             

            Advice me!

             

            TQ

             

             

            • 3. Re: Retrieve data by date
              mrvodka
                 Well if you just want to search by "5" then all your calc has to do is be Month (invoice_date) and search in there. But this will find all records in May, not just for this year.
              • 4. Re: Retrieve data by date
                mayor
                  

                Well, what I intend to do is try to use 2 fields for month and year for searching.

                What I did like this:

                 

                month(invoice_date)

                year(invoice_date)

                 

                But when i browse the layout, no data is displayed for these 2 fields.

                I'm also cannot search by these 2 fields as no data is found.

                 

                Your advice much appriciated!

                TQ 

                • 5. Re: Retrieve data by date
                  LaRetta_1
                    

                  "Well, what I intend to do is try to use 2 fields for month and year for searching."

                   

                  If you have a true date field, you can search for 5/2009 and get all records for May, 2009.  You can script it as well.  So you can provide your Users month and year global fields and then search portion of your script could be:


                  Enter Find Mode [ uncheck pause ]

                  Set Field [ yourDateField ; globalMonth & "/" & globalYear ]

                  Perform Find [ ]

                  • 6. Re: Retrieve data by date
                    mayor
                      

                    Thanks LaRetta, it works! :manvery-happy:

                     

                    TQ