Record Selection Difficulties 2

[Bennie47250]

Using Crystal 7

I have always told anyone that asked that I have not found anything Crystal can’t do; well I think I have finally done it.

In a table we have a field name “TypeOfInspection” and it that field will ether be an F or S. I have been asked to develop a report that will prompt the user to enter an F to get just the F records or enter an S to get just the S records or enter an B to get both types of records.

I thought I could do this by creating a Parameter that prompts for the F, S or B and then have a formula that passes the value to the field in the select expert. I have been able to get this to work to pass ether an F or an S but I can’t get the both to work.

The formula that passes the value is

if {?RecordTypeForInspection} = 'S' then "S"
else if {?RecordTypeForInspection} = 'F' then "F"
else if {?RecordTypeForInspection} = 'B' then "F","S"

In the record selection the selection formula is

{@Year} = {@CurrentYear} and
{Inspection_and_Receipt_Records.TypeOfInspection} in ["{@RecordSelection}"] and
{Inspection_and_Receipt_Records.VNDNO} = 3202.00

(I’m using the IN operator to allow me to use “F","S" when B is selected.)

Any thoughts on how I can do what I’m attempting to do using my method or another?

Thanks
Bennie

 

[synapsevampire]

Just use the record selection formula, without a formula, as in:

(
{@Year} = {@CurrentYear}
)
and
(
if {?RecordTypeForInspection} = 'S'
or
{?RecordTypeForInspection} = 'F' then
{Inspection_and_Receipt_Records.TypeOfInspection} =
{?RecordTypeForInspection}
else
if {?RecordTypeForInspection} <> 'S'
and
{?RecordTypeForInspection} <> 'F' then
true
)
and
(
{Inspection_and_Receipt_Records.VNDNO} = 3202.00
)

Check the Database->Show SQL Query to ensure that the SQL is properly passed.

-k

 

[rhinok]

You can make SV's statement even simpler by using the following:

(
{@Year} = {@CurrentYear}
)
and
(
if
  {?RecordTypeForInspection} <> 'B' 
then {Inspection_and_Receipt_Records.TypeOfInspection} =
  {?RecordTypeForInspection}
else if 
  {?RecordTypeForInspection} = 'B' 
then
  true
)
and
(
{Inspection_and_Receipt_Records.VNDNO} = 3202.00
)

~Kurt

 

[synapsevampire]

Mae sure that you verify the SQL pass thru in Kurt's statement, I can't test but it may not pass, the method I use is universal for numerous Crystal versions.

-k

 

[Bennie47250]

Thanks for the suggestions. As I review each code I can see where synapsevampire and Kurt is going but... each code will only return the B records. When I use ether an S or F no records are returned.

When I Check the Database->Show SQL Query the following is displayed when using synapsevampire suggestion

SELECT
Inspection_and_Receipt_Records.`VNDNO`, Inspection_and_Receipt_Records.`SumOfP4QRC`, Inspection_and_Receipt_Records.`RECDT`, Inspection_and_Receipt_Records.`Inspection Or Receipt`, Inspection_and_Receipt_Records.`TypeOfInspection`,
Local_Vendor_Master.`VNAME`
FROM
`\\web5\d$\PartsPerMillion\Parts Per Million`.`Inspection and Receipt Records` Inspection_and_Receipt_Records INNER JOIN `\\web5\d$\PartsPerMillion\Parts Per Million`.`Local Vendor Master` Local_Vendor_Master ON
Inspection_and_Receipt_Records.`VNDNO` = Local_Vendor_Master.`VNDNO`
WHERE
Inspection_and_Receipt_Records.`VNDNO` = 3202
ORDER BY
Local_Vendor_Master.`VNAME` ASC

I notice that the record select is not being passed. When using Kurt’s formula the record select is also not passed.

I see that I failed to mention that the records are stored in an Access 2000 database in my original post but I'm using a ODBC driver (PDSODBC.DLL) to connect. Could this be making a difference?

Thanks again.
Bennie

 

[Bennie47250]

Never mind, the code does work; someone had changed the codes in the test data.

a BIG thanks TO synapsevampire and Kurt