Record Select formula with wildcard 1

[CalgaryCR9]

Crystal 9.

I have a field called {SYSTEM_ITEM.ITEM_SERIAL_NO}.

A sample is {SYSTEM_ITEM.ITEM_SERIAL_NO}: FM44740130. However, the possiblities are endless.

I need to create a record selection formula looking for those {SYSTEM_ITEM.ITEM_SERIAL_NO} with the range of *412* to *606* inclusive. Such a formula would have picked up my sample serial number FM44740130.

NOTE: The 412 through to 606 range is the first numeric character in the field {SYSTEM_ITEM.ITEM_SERIAL_NO}. ie: in this 10 character serial number, I need to only search the 3rd through the 5th characters, ignoring the last 5 characters entirely.

 

[lbass]

If the relevant numbers are always in positions 3 to 5, then you could use a formula like:

val({SYSTEM_ITEM.ITEM_SERIAL_NO}[3 to 5]) in 412 to 606

-LB

 

[synapsevampire]

I wouldn't convert it to a numeric, it probably won't pass the criteria for processing on the database.

{SYSTEM_ITEM.ITEM_SERIAL_NO}[3 to 5]) in "412" to "606"

should pass it corrrectly.

if not, create a SQL Expression to return only the first 3 characters, as in:

substr(table.field,3,3)

and then in the record selection use:

%MyField in "412" to "606"

Or you could even cast it to a numeric data type.

cast(substr(table.field,3,3) as numeric)

Of course this is database dependent, it's best to post your database type and the connectivity as well.

-k

 

[CalgaryCR9]

PERFECT!!!! Exactly what I needed. I have 382 systems in our database that need service based on a vendor notifiaction sent.

You are AMAZING!!!!