Multiple Criteria IF Statement

[nelliott]

I am trying to write a script that checks if one word is nearer the start of the field than 2 other words. I have tried the following but it doesn't seem to work

IF Position(Text,"XXX",1,1) < Position(Text,"YYY",1,1) or Position(Text,"XXX",1,1) < Position(Text,"ZZZ",1,1)

Set Field ......

End IF

It works fine if I am only comparing it to 1 word i.e. everything before the 'or' but does not want to work for multiple words

Any ideas?

Cheers

Nick

[CobaltSky]

Your current formula syntax is ascertaining whether XXX is nearer to the start of the field than either one of the other words.

If you want to ensure that it is nearer than both the other words, you will have to use the and operator rather than or - thus:

If ["Position(Text,"XXX",1,1) < Position(Text,"YYY",1,1) and Position(Text,"XXX",1,1) < Position(Text,"ZZZ",1,1)"]

[nelliott]

No, I am trying to ascertain whether the first word is in front of either of the other words as the field will contain either of the other words but unlikely to contain both

[CobaltSky]

Thanks for clarifying. I think perhaps I have got a little closer to understanding what it is you are looking for.

Perhaps you would like to try:

Position(Text,"XXX",1,1) < Min(Case(Position(Text,"YYY",1,1), Position(Text,"YYY",1,1)), Case(Position(Text,"ZZZ",1,1),Position(Text,"ZZZ",1,1)))

I've tested it out in a file here (see copy attached to this message) and it appears to work for all cases.

XYZ.zip