Finding ranges of information

Finding, sorting, and replacing data > Finding records > Finding ranges of information

You can find ranges of information, such as all records that contain a field value that is greater or less than a number, or between two particular dates. A range is different based on the data type:

•Numbers: least to greatest

•Dates, times, and timestamps: earliest to latest

•Text: first to last word, based on the index order of words (not the sort order). A word can be a single character; for example, a find on a field for <M would return every record that contains a word less than M in the field’s index order.

To find ranges of information:

1. Start a find request.

See Making a find request.

2. Refer to the following table for examples of different ways to search for a range of information.


To find values that are	Use this operator	Examples
Less than a specified value	<	<40 <9/7/2014 <M
Less than or equal to a specified value	<= or ≤	<=95129 ≤05:00:00 <=M
Greater than a specified value	>	>95129 >9/7/2014 >M
Greater than or equal to a specified value	>= or ≥	>=100 >=9/7/2014 ≥8:00 ≥M
Within the range you specify	.. or ... (two or three periods)	12:30...17:30 1/1/2014..6/6/2015 A...M Mon..Fri
Within the sub-range you specify	{..} or {...} (two or three periods)	7/{1...15}/2014 {1..3}/{10..16}/2015 12:{30..45} {7...9}:15 PM
Based only on certain date or time components such as month, year, or minutes (see more examples below)	* or type nothing for each component you don’t want to specify	3//2014 2/ 2014 *:30 PM

3. When you’ve entered the find criteria that you want, click Perform Find in the status toolbar, or choose Requests menu > Perform Find.

Notes

•When searching for ranges of information in date, time, and timestamp fields, you don’t have to specify all date or time components. For example, you can type 5/2014 instead of 5/1/2014...5/31/2014 to find all dates in May 2014.

•You can combine operators to simplify range searches. For example, type */{10..15}/2014 to search for all dates in 2014, but only for days from the 10th through the 15th.

•For information on writing scripts to find a range of dates, search the FileMaker Knowledge Base available at http://help.filemaker.com.

•Refer to the following tables for examples of different ways to search for dates, times, and timestamps.


To find dates	Type this in the field
In June 2014	6/2014
From July 2014 through October 2015	7/2014...10/2015
That occur on a Friday	=Friday
From the 10th through the 16th of October or November 2014	{10..11}/{10..16}/2014
That occur on March 1st between 1868 and 1912 in the Japanese Emperor Year era of Meiji	m*/3/1
That occur on December 31st between 1930 and 1940 in the Japanese Emperor Year era of Showa	S{5..15}+12+31


To find times	Type this in the field
In the 3 o’clock hour, not including 4:00 PM	3 PM
Between 8:00 AM and 8:59:59 PM	8 AM...8 PM
In the morning	AM
Any of the times 4:30, 5:30, and 6:30 PM	{4..6}:30 PM


To find timestamps	Type this in the field
In the 3 o’clock hour today	// 3 PM
In the 7 o’clock hour in May 2014	5/2014 7 PM
That occur on a Monday in 2015	=Mon 2015
From the 10th through the 16th of November 2015 and from 3:00 PM to less than 6:00 PM	11/{10..16}/2015 {3..5} PM