Formula? Field starts with ABC, but does not end with LMN

[JonAtHQ]

Hello,

I'm lost. I'm looking for a formula that will print records if the value in field begins with "ABC" but will exclude records if the value in the same field ends with "LMN".

Any ideas?

 

[kray4660]

I am assuming that you would want a record selection formula.

if left({field},3)="ABC" and right({field},3)<>"LMN" is one possible solution.

I hope this helps.

 

[MCuthill]

JonAtHQ,

Kray4660 is nearly spot on. For a Record Selection, you would not require the "if".

Record Selection:

[blue]Left[/blue]({Table.YourField},3)="ABC" [blue]AND Right[/blue]({Table.YourField},3)<>"LMN"

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

JonAtHQ,

In thinking a little further, do you want only those instances where the characters are UPPERCASE? If the field has non-UPPERCASE, would you want it to force it to UPPERCASE and still report?
For example:
[tt]Record # ... Table.YourField
Record 1 ... ABCGhPOP
Record 2 ... AbCGHPOP
Record 3 ... abcGHLMN[/tt]

At any rate, if you wish to convert the values of the text field to UPPERCASE before performing your evaluation, change your record selection to as follows:

[blue]Left[/blue]([blue]UpperCase[/blue]({Table.YourField}),3)="ABC" [blue]AND Right[/blue]([blue]UpperCase[/blue]({Table.YourField}),3)<>"LMN"

With the above, the ficticious records would look as follows prior to evaluation:
[tt]Record # ... Table.YourField
Record 1 ... ABCGHPOP
Record 2 ... ABCGHLMN
Record 3 ... ABCGHLMN[/tt]

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[kray4660]

From my experience, if you are dealing with an MSSQL, it is case insensitive.

 

[JonAtHQ]

Thanks for the formula. We'll give it a try. For some reason I was trying to build the formula with "STARTS WITH", and something else. Too bad there isn't an "ENDS WITH". That would make my life a lot easier.