troubleshooting Question

Excluding data from one table field looking up that value from another

Avatar of MikeM670
MikeM670 asked on
Crystal Reports
6 Comments1 Solution134 ViewsLast Modified:
Using Crystal Reports IX with SQL Server 2014

 I need to exclude a value based on the value stored in a table field where I lookup the value in another table.  Since this report would be run by multiple agencies and they use their own user assigned value for a "House Watch" I need to find that value from the param3 field in the table ListUCR.


Table and Field I will be excluding data on:    
Table Name: CaseMaster
Table Field: UCRCodeAll   (Varchar)

7725,7741
7725
7314,7725
7314,7741,7725

Table and Fields I am looking up to obtain the value in  the Code field based on a string value in a param3 field.

Table Name: ListUCR
Table Fields: Code        (Char)
Table Field Param3      (Varchar)

Example Department 1

Code            Description                     Param3
7225            HOUSE WATCH               HOUSE WATCH     (Query would use 7225)

Example Department 2

Code            Description                     Param3
7845           HOUSE WATCH               HOUSE WATCH     (Query would use 7845)


I can get it to work if I hardcode a particular value into the query:
not ({CaseMaster.UCRCodeAll} like "*7225*")

But when I try the below listed query it fails to return any records.
{CaseMaster.UCRCodeAll} <> {ListUCR.Code} and ({ListUCR.Param3} Like 'HOUSE WATCH')


Mike
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros