Originally Posted by
John_G
Hi -
Since the string "__d4" does not always occur in the part_no field, it's a bit complex, but this expression should work:
iif(instr(part_no,"__d4") = 0, part_no, left(part_no , instr(part_no & "__D4", "__d4") -1 ))
The reason I put the & "__d4" in the second part is that the IIF evaluates both the True and False of the expression, and if the "__D4" is not in the part_no, the left() function generates an error. If "__D4" is already in the part_no, adding the second bit does not matter, since instr() gives you the first occurence. I gave it a quick test in A2003, and it seems to be fine.
John