dphillips,

Yes, you are probably right that using CHARINDEX alone may be a more exact translation of the Access behaviour. It's a while since I used Access so I can't remember how Access deals with trailing whitespace. However, the 2 expressions don't always give the same result in T-SQL because trailing whitespace is not counted by the T-SQL LEN function, as demonstrated by the following example.

DECLARE @s-2 varchar(100)

SELECT @s-2 = 'Option1 or Option2' /* NB 3 spaces between 'Option1' and 'or' */

SELECT SUBSTRING(@s, 1, CHARINDEX(' or', @s-2) - 1),

LEN(SUBSTRING(@s, 1, CHARINDEX(' or', @s-2) - 1)),

CHARINDEX(' or', @s-2) - 1

[font="Courier New"]SubField Length CharIndex

----------------------- ----------- -----------

Option1 7 9[/font]