INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Windows7: Version: CRXI / Experienc

Windows7: Version: CRXI / Experienc

(OP)
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


RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

(OP)
Thanks. That did work after I changed the brackets. Thanks.

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

(OP)
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"

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

(OP)
Thanks Betty. It saves me time in finishing this.

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

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

RE: Windows7: Version: CRXI / Experienc

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close