Windows7: Version: CRXI / Experienc 5

[wld1957]

Windows7: Version: CRXI / Experienced user but has been awhile since I have worked on reports.

I have been trying to create a formula that would search a memo field for part of a word "weapon" and then return a value of "W". The word "WEAPON" may be entered in either uppercase or lowercase in the memo field.

I tried: If InStr ({CAS_ActionEntity.ActionEntityNote}, 'WEAPON') > 0 then "W"
This brings back the desired results but only if the search word is in upper case. I would like to include those have have been entered in lower case also.

I also tried a number of other variations with lowercase to include those records that may have been entered in lower case but the formulas have not worked. I searched the forum and tried the posts that best fit what I was looking to do with no success.

Any suggestions would be greatly appreciated. Thanks.

Bill

 

[BettyJ]

Try:
[tt]if ("WEAPON") in uppercase({CAS_ActionEntity.ActionEntityNote)} then "W"[/tt]

 

[wld1957]

Thanks. That did work after I changed the brackets. Thanks.

 

[Charliy]

Having done similar projects, I suggest you also scan for "alternate" spellings of "Weapon

 

[wld1957]

Thanks, that is a good point. Something I did not think about. I assume I would use the same formula with different spelling variations?

if ("WEAPON") in uppercase({CAS_ActionEntity.ActionEntityNote}) then "W"
or
if ("WAEPON") in uppercase({CAS_ActionEntity.ActionEntityNote}) then "W"
or
if ("WEAPN") in uppercase({CAS_ActionEntity.ActionEntityNote}) then "W

 

[BettyJ]

[pre]if ( "WEAPON" in uppercase({CAS_ActionEntity.ActionEntityNote}) or
"WAEPON" in uppercase({CAS_ActionEntity.ActionEntityNote}) or
"WEAPN" in uppercase({CAS_ActionEntity.ActionEntityNote}) )
then "W" ;[/pre]

 

[wld1957]

Thanks Betty. It saves me time in finishing this.

 

[kray4660]

I always try to examine the raw data when there is user input to hopefully catch all the possible variations.

 

[wld1957]

Also a good point. Unless the user is limited in their input the chance of errors is going to be present. Thanks.

 

[pmax9999]

The issue you will have, is that it is impossible to anticipate what will get entered into text fields, and your results will reflect this.

Depending on your database and experience with SQL (Commands / SQL Expressions), one function you might want to investigate is SOUNDEX which can be useful in searching for text where you are not certain of its spelling.


Hope this helps.


Cheers
Pete

 

[AndyMc]

Using the Crystal SOUNDEX function that Pete suggested would have treated all of your Weapon spellings ("WEAPON" "WAEPON" and "WEAPN") (also "Wepin","Wepon","Weapom","Wepom",...) as Weapon. They all have a soundex value of W150

If SOUNDEX({yourParameter}) = SOUNDEX("WEAPON") then "W";

-Andy

 

[pmax9999]

Thanks Andy. I'm, at work at the moment and didn't have the time to expand on the SOUNDEX approach with an example (and wasn't even certain it was a native Crystal function but new it was available in most databases) but thought it might be useful anyway.

Cheers

Pete

 

[wld1957]

Thanks - I will also try this approach and see what results I come up with. These are ideas I never would have thought of without the help of others. Not only does this help solve my problem but is also a learning experience. Thanks again.