'in' and 'not in' issue

[tedi1]

Can I use 'in' and 'not in' in CR 8.5 and if the answer is yes how can I use it.

I haveing a problem: I have wrote a sql query that contins 'in' and 'not in' and I'm getting diferrent results when I run it in the SQL SERVER and in the CR tool. my guss is that the problem is with the 'in' and 'not in'

 

[dgillz]

Below are two examples using crystal syntax:

Using 'in':
{DateField} in Date(2002,1,1) to Date(2002,12,31)

Using 'not in':
Not ({Datefield} in date(2002,1,1) to Date(2002,12,31))

Both of the above formulas will return true if the date in is in the year 2002. Please let me know if you have any questions. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[tedi1]

I need to use the 'not In' not with date but with a range of numbers: I have a table that contanies a customer nubser and i want to know if a certain number is not in that table. as you can see I don't know the exact range of customer numbers.

thanx in advance for your help

 

[dgillz]

You know the customer number, but you don't know if the customer number is in the table in question?

Since crystal reports cannot report on data that does not exist in the database, what are you expecting the report to show you? Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[tedi1]

I'll explain it better (So I hope): I have Two Tables that holds Customer numbers. one of them Holds all the customer numbers ("big table&quot and the other Hold only some of then due to a certain report we have on the customer ("small table&quot. I would like to get all the customers (from the "big" table) that doesn't appear in the "small table".

how do I do this?

 

[dgillz]

Place both tables on a report, and link by customer number. Write a very quick report with 2 columns that has the customer number from both tables on it. Sort the report by the column that you want to see if the customer does not exist. All the blanks should be at the top.

Alternatively, use a record selection formula:
IsNull({Table2.CustomerNumber})

This should return only those records without a matching record in table 2. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[krishna]

Hi

Try this one

1) Link the 2 tables using Customer No
2) Change the link option to Left/Right Outer Join
3) Place the field from the big table in the report
4) Set the record selection formula to SmallTable.CustomerNo =0

This would give you all the customers not in the small table

Regards
Krishna Kumar

 

[tedi1]

Thank you both, will try both your suggestions