I need to filter text from within a

[davismar]

I need to filter text from within a field name called: tblServiceOrders.GeneralResolutions - this is a memo field
in Crystal Reports XI.

Within the text of the database, I need to set certain criteria where the text contains the following ONLY:

WS
Windstream
DID

Can someone tell me what the proper formula would be?
Also, if the return of the formula = true or false, how can I display only the records that meet this specific critera?

Thanks!

See attached report for the example

 

[MCuthill]

DavisMar,

Not 100% for Crystal Reports XI (as I use 10), but the following should identify if all the strings exist in your field. You should then be able to filter the report on this formula field.

{@SubStringsExist_YN}

[blue]IF[/blue] 
(
   [blue]InStr[/blue]({tblServiceOrders.GeneralResolutions},"WS")>0 [blue]AND [/blue]
   [blue]InStr[/blue]({tblServiceOrders.GeneralResolutions },"Windstream")>0 [blue]AND[/blue]
   [blue]InStr[/blue]({tblServiceOrders.GeneralResolutions },"DID")>0 
)
[blue]THEN[/blue] "Y" 
[blue]ELSE[/blue] "N"

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."

 

[MCuthill]

oops. Please remove the last space from the field name in the 2nd and 3rd "AND" clauses.

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."

 

[jlopeman]

The above solution will work as long as each record includes WS, Windstream, and DID in the memo. If its possible to have different permutations, i.e., includes WS and DID but not Windtream, then you will want to use "OR" statements instead of "AND

 

[davismar]

Thanks for the responses.

Once more clarification -

If I want to use only WS and exclude results that contain WS - how would I need to change this formula?

For example - if WS is found within TWS, I want to exclude TWS and only pull records with WS only?

Thanks!

 

[Madawc]

Create a formula field saying

tblServiceOrders.GeneralResolutions = ["WS", "Windstream", "DID"]

Display it on unselected data, it should say True or False as expected.

Then add @YourSelect to your record selection - it is a boolian, no need to explicitly test the result.

(If you did want text within a larger string, you would say "IN"

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[MCuthill]

davismar,

If I want to use only WS and exclude results that contain WS - how would I need to change this formula?

For example - if WS is found within TWS, I want to exclude TWS and only pull records with WS only?

For something like this (I presumed exclude TWS in addition to including the other three), you would change the formula to:
{@SubStringsExist_YN}

IF 
(
   InStr({tblServiceOrders.GeneralResolutions},"WS")>0 AND 
   InStr({tblServiceOrders.GeneralResolutions },"Windstream")>0 AND
   InStr({tblServiceOrders.GeneralResolutions },"DID")>0 AND
   InStr({tblServiceOrders.GeneralResolutions },"TWS")=0 AND
)
THEN "Y" 
ELSE "N"

InStr() returns a 0 if a substring does not exist within the full string (and a 1 when it does exist), I think the above is what you seek.

Another option would be that if WS is always preceeded and followed by a space, you could change the first InStr() statement to the following (instead of adding the TWS exclusion):

...InStr({tblServiceOrders.GeneralResolutions}," WS ")>0 AND...

Cheers! Hope this helps!

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."