Find records between two dates in FileMaker

1. Put records on both sides of the boundary.

Create five tasks with these dates, entering them in the date format expected by your file. The first and last deliberately sit outside the requested month:

A — 30 September 2026
B — 1 October 2026
C — 15 October 2026
D — 31 October 2026
E — 1 November 2026

The expected found set is B, C and D. If you test only C, almost any overly broad range could appear to work. Include an empty DueDate as a sixth record; it should not be part of this month’s dated tasks.

2. Prove the range in Find mode.

On a layout based on Tasks, enter Find mode and put a start date, three periods, and an end date in DueDate. For a file using month/day/year, the criterion is 10/1/2026...10/31/2026. For day/month/year, use 1/10/2026...31/10/2026. Perform the find and inspect the three expected records.

Confirm that DueDate really is a Date field. A text field containing date-looking strings is not an equivalent test. Also inspect the stored year: a layout that hides the year can make an old record look current.

3. Build the criterion from date values.

For a scripted search, create real date values first rather than concatenating ambiguous date fragments. The Date function’s argument order is month, day, year. This calculation returns the range text for the chosen dates:

Let (
    [
        startDate = Date ( 10 ; 1 ; 2026 ) ;
        endDate = Date ( 10 ; 31 ; 2026 )
    ] ;
    GetAsText ( startDate ) & "..." & GetAsText ( endDate )
)

Evaluate and inspect the result in the file where the find will run, particularly if it was created with different system formats. After proving the literal example, replace the two dates with validated Date inputs. Reject blank inputs or a start date later than the end date before entering Find mode.

4. Give the scripted find a clear outcome.

A useful sequence is: validate the inputs, move to the intended Tasks layout, enter Find mode without pausing, set DueDate to the criterion, perform the find, and immediately capture the error. Handle a no-match result separately from an unexpected failure.

Keep the first version small. Do not add sorting, record editing and navigation to another window until the search itself passes. If the screen promises “Due this month”, also show the actual start and end dates so users can see which period the report represents.

See the error-handling walkthrough for the difference between capturing an error and silently ignoring it.

5. Do not quietly reuse it for timestamps.

A date-only field answers a calendar-date question. A timestamp adds a time component, and imported API values may also involve a time-zone conversion. Define which time zone and business date the report represents before adapting this search.

For a timestamp workflow, add tests at the start of the first day, late on the last day, and exactly at the next day’s boundary. Do not assume that a displayed date proves the hidden time is included. Keep that as a separate fixture rather than changing the field type underneath this example.

• Expected month: exactly B, C and D.
• Range with no records: a clear no-match outcome.
• Reversed or missing inputs: validation before the find.
• Different file/system date formats: the same intended calendar dates.

Continue reading

• Handle FileMaker script errors without hiding the problem
• Write your first FileMaker ExecuteSQL query