Using Like, In, and wildcard range

[Trogg]

Hello all...
I am using Crystal reports 2011. I have a range of string values for example C00* to C43* that I need to capture. "IN" alone does not seem to get them all(ie. Table.field IN 'C00*' to 'C43*'). It seems to stop at C43 with out picking up any values such as C43110. IS there any way to combine "like" and "in" to get all matching values or some other way to accomplish this? Thanks in advance for your help!

 

[SkipVought]

Hi,

Maybe...
[tt]Left(Table.field, 3) Between 'C00' And 'C43'[/tt]

An In() is a distinct list, not a from - thru.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Trogg]

Thank you Skip but Between does not seem to be a function or operator.. it does nothing.

 

[SkipVought]

[tt]Left(Table.field, 3) >= 'C00' And Left(Table.field, 3) <= 'C43'[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[lbass]

Try using a value above the one you want for the second clause, e.g., if you only want values up through C43*, then use a selection formula like this:

{table.field} >= 'C00*' and
{table.field} < 'C44*'

-LB

 

[Trogg]

Skip... I was not totally aware that "In" produced distinct results only but it does make sense... thanks for your alternate solution.. it does work but it is unfortunately very slow considering the amount of data and time frame I am searching. Lbass' solution works for me using Table.field IN 'C00*' to 'C44*'. By extending it from 43 to 44 I get all the 43's I was not getting that go past 43 (ie. 43112 etc.) Sometimes the simplest solution evades us doesn't it? Thank you both for your excellent help.