Need to look up a parameter(s) in a field: instr & parameter help 1

[wso]

I am a beginner user of Crystal Reports version 8.5, accessing multiple tables in an Access 2003 database.

I need to run reports based on (selection criteria) 2 parameters {?Product Code} (an string) and {?Status Code} (a string). In the Select Expert, the statement I entered below works if I'm entering in each {?Status Code} at a time. There are 12 Status Codes total. I limit what the user can enter by using a dropdown menu.

{Product.ProductCode} = {?Product Code} and
instr({Customer.StatusCode},{?Status Code}) <>0

I used instr because in Customer.StatusCode, there can be multiple Status Codes in the same field.

If the user enters "ALL" in the {?Status Code} prompt, I want to be able to produce all 12 reports; 1 report for each Status Code for the Product Code selected.

Thanks in advance for your help!

 

[synapsevampire]

Try:

(
{Product.ProductCode} = {?Product Code}
)
and
(
if {?Status Code}[1] <> "All" then
instr({Customer.StatusCode},{?Status Code}) > 0
else
if {?Status Code}[1] = "All" then
true
)

-k

 

[wso]

Hi synapsevampire, thanks for the post! I tried your formula, but it didn't return any records.

In the Product.ProductCode field it's either A or B

The Customer.StatusCode field, there can be X111:A123:Y111 all in one field or any combination of Status Codes separated by colons. I need to create a separate report for X111, then one for A123, then one for Y111. Grouped by their Status Code, so the record with X111:A123:Y111 will appear 3 times, once in the X111 report, once in the A123 report and once in the Y111 report.

 

[synapsevampire]

Please state what didn't work.

If they passed All, then it should return all of the statuses.

What you're proposing is better done on the database to fix the silly way they've stored the data, then you can treat it as intelligent data.

You'll want to parse the data out so that you have seperate rows for each in another table.

-k