3 Replies Latest reply on Dec 31, 2010 3:10 PM by JohnMulder

    Find of data between a start_date and end_date

    JohnMulder

      Title

      Find of data between a start_date and end_date

      Post

      Ref:

      Use FM Pro 9

      Coming (with difficulty) from an MS Access background.

      I have a simple time billing database, which tracks hours worked on specific days.

      Fields: Date, Time_start, Time_finish, hours(calculation) etc

      I want to generate a report of hours worked between different dates -viz: fortnight blocks Mon-Sunday. In Access I would make a form (layout) which had 2 non-linked text boxes for Start_date and End_date, then write a query which looked at the dates in these text-boxes to specify which date range to search - a find.

      Can you have text boxes in FM which are not linked to a table/field?

      If you can, then can you then perform a find from the values in those text boxes?

        • 1. Re: Find of data between a start_date and end_date
          davidanders

          Filemaker finds a span of dates by 1/1/2000...1/31/2000 will find Jan 1 thru Jan 31, 2000

          If you have a field StartDate and another field EndDate and another field (calculation - StartDate & "..." & EndDate) a Find based on this calculation field will return January 2000 records.

          • 2. Re: Find of data between a start_date and end_date
            philmodjunk

            Can you have text boxes in FM which are not linked to a table/field?

            If you can, then can you then perform a find from the values in those text boxes?

            In FileMaker, we use field with global storage field options for this purpose. Often, it's useful to define a separate table just for these global fields.

            Here's a sample script that uses the dates in two global date fields: gDate1 and gDate2 to perform a date range query:

            Enter Find Mode[] // clear the pause checkbox
            Set Field [YourTable::YourDateField ; YourTable::gDate1 & "..." & YourTable::gDate2]
            Set Error capture [on] // suppress the error dialog if no records are found
            Perform Find[]

            Keep in mind that the set field step won't work unless gDate1 and gDate2 have been defined with the Global Storage field option.

            • 3. Re: Find of data between a start_date and end_date
              JohnMulder

              Hi PhilModjunk,

              Thanks for that solution. Was exactly what I was looking for.

              Regards,

              jwamSmile