Need help with LOCATE statement

[Patrick Kelly]

The following LOCATE statement will only find values in the field References if the only value in the field References is the value to be found. If other values exist in the field References as well as the sought after value, then the LOCATE statement does not find the sought after value.

Example 1: If the value sought after equals Spindle 12:104 and the only value in the References field is Spindle 12:101-104 then the value sought after is found.

Example 2: If the value sought after equals Spindle 12:104 and the values in the References field are

; Maxten 111:94 ; Tendor 12:93 ; Spindle 12:101-104 ; Trund 11:22 ;

The LOCATE statement does not see the sought after value.

As an example, variable values would equal:
$PartA1 -= Spindle
$PartB1 = 12
$PartC1 = -104

The LOCATE statement...

OR (LOCATE('-', `References`) > 0 AND LOCATE('". $PartA3 . $PartB3 . ":" ."', `References`) > 0 AND ". $PartC3 ." BETWEEN SUBSTRING_INDEX(SUBSTRING_INDEX(`References`, ':', -1), '-', 1) + 0 AND SUBSTRING_INDEX(`References`, '-', -1) + 0) AND `Source` = $BT


I believe the problem lies in the fact the LOCATE statement is concerned with locating only the parts of the strings assuming no other field values exist rather than locating parts of the strings within the field containing other values. Sought after values in the field are always bracketed by a semi-colon.