LOCATE command
Concept Information
Searches for the first record that matches the specified value or condition, or moves to the specified record number.
Syntax
LOCATE {IF test <WHILE test> <FIRST range|NEXT range>|RECORD num}
Parameters
| Name | Description |
|---|---|
|
IF test |
The value or condition to search for. You must enclose character literal values in quotation marks, and datetime values in backquotes. |
|
WHILE test optional |
A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached. |
| FIRST range | NEXT range optional |
The number of records to process:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. |
|
RECORD num |
The record number to locate. |
Examples
Locate the first record that matches a specified value
The following examples illustrate using LOCATE to find the first occurrence of a specific value in a table:
LOCATE IF Vendor_Name = "United Equipment"
LOCATE IF Vendor_Name = "Uni"
LOCATE IF Invoice_Amount > 1000
LOCATE IF Invoice_Date = `20141231`
Locate the first record that matches a specified condition or expression
The following examples illustrate using LOCATE to find the first occurrence of a specific condition or expression in a table:
LOCATE IF Vendor_Name = "United Equipment" AND Invoice_Amount > 1000 AND Invoice_Date > `20140930`
LOCATE IF Vendor_City = v_city
Locate a record by record number
The following example illustrates using LOCATE to move to a particular record in a table:
LOCATE RECORD 50
Remarks
For more information about how this command works, see Selecting the first matching record.
How it works
Use the LOCATE command to move directly to the first record in a table matching the specified value or condition.
If the specified value or condition is found, the first matching record in the table is selected. If the specified value or condition is not found, the table is positioned at the first record.
You can also use LOCATE to move directly to a specific record number
LOCATE compared to FIND and SEEK
Unlike the FIND and SEEK commands, the LOCATE command is not restricted to searching an indexed table, or a single character field. Using LOCATE, you can search for any type of literal, or for an expression that uses any data type, or mix of data types.
When used to search an unindexed table, the LOCATE command may be significantly slower than FIND or SEEK because it must process each record in the table sequentially. The required processing time depends on the size of the table, the location of a matching record, and whether you reduce the scope of the search by using WHILE, FIRST, or NEXT.
Partial matching supported
Partial matching is supported for character searches. The search value can be contained by a longer value in the field or fields being searched. However, search values must appear at the start of fields to constitute a match.
Enabling or disabling partial matching
You can enable or disable partial matching using either the SET command, or a setting in the Options dialog box:
| Enable partial matching | Disable partial matching |
|---|---|
|
Specify: SET EXACT OFF or Deselect: Exact Character Comparisons in the Options dialog box (Tools > Options > Table) Result: The search value can be contained by a longer value in the field or fields being searched. The search value must appear at the start of a field to constitute a match. |
Specify: SET EXACT ON or Select: Exact Character Comparisons in the Options dialog box (Tools > Options > Table) Result: The search value must exactly match a value in a field to constitute a match. |
For more information about SET EXACT, see SET command.
For more information about the Exact Character Comparisons option, see Table options.
