LOCATE command

Used to search for the first record that matches the specified value or condition, or used to move to the specified record number.

Syntax

LOCATE {IF test <WHILE test> <FIRST|NEXT range>|RECORD num}

Parameters

IF test

Specifies the value or condition to search for. You must enclose character literal values in quotation marks, and datetime values in backquotes.

WHILE test

Optional. Specifies that the command is executed only while a condition is true. The command processing terminates as soon as the specified condition evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

RECORD num

Specifies the record number to locate.

Remarks

Use the LOCATE command to move directly to the first record in a table matching the specified value or condition, or to move directly to the specified record number.

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 the FIND or SEEK commands 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 the WHILE, FIRST, or NEXT parameters.

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.

Partial matching is supported for character searches – that is, 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. Partial matching is enabled when SET EXACT = OFF. If SET EXACT = ON, partial matching is disabled and the search value must exactly match a value in a field to constitute a match. For more information about SET EXACT (the Exact Character Comparisons option), see SET command.

If you want to ensure that the Exact Character Comparisons option is not used with the LOCATE command, check that the option is deselected in the Table tab in the Options dialog box (Tools > Options). If you are using a script, you can add the SET EXACT OFF command before the LOCATE command appears. If required, you can restore the previous state with the SET EXACT ON command.

Examples

a. Locating 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 record.

LOCATE IF Vendor_Name = "United Equipment"

LOCATE IF Vendor_Name = "Uni"

LOCATE IF Invoice_Amount > 1000

LOCATE IF Invoice_Date = `20141231`

b. Locating 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 record.

LOCATE IF Vendor_Name = "United Equipment" AND Invoice_Amount > 1000 AND Invoice_Date > `20140930`

LOCATE IF Vendor_City = v_city

c. Locating a record by record number

The following example illustrates using LOCATE to move to a particular record in a table.

LOCATE RECORD 50

Related reference
FIND command
SEEK command


(C) 2015 ACL Services Ltd. All Rights Reserved.