If, else if not returning correct data

[bluevelvet]

Can anyone help. I have the following if statement:

{@DateOriginal} = {?DateOriginal} and

(if{?StockNumber}="" then {IPPOITM.STOCK_NUMBER}like"*" and {IPPOITM.VENDOR_NUMBER}like{?Vendor}

else if {?StockNumber}<>&quot;&quot;then{IPPOITM.STOCK_NUMBER}like{?StockNumber}and {IPPOITM.VENDOR_NUMBER}like {?Vendor}

else if {?Vendor}=&quot;&quot; then {IPPOITM.VENDOR_NUMBER}like &quot;*&quot; and {IPPOITM.STOCK_NUMBER}like {?StockNumber}

else if {?Vendor}<>&quot;&quot; then {IPPOITM.VENDOR_NUMBER}like{?Vendor} and {IPPOITM.STOCK_NUMBER}like {?StockNumber})

The idea is that you should be able to pass a date parameter and then choose whether or not to provide stock numbers and vendors. If you leave the stock number or vendor parameter empty it should return all within the constraints of the other parameter values passed.

These are the results I am getting:

Give a date range - no records returned
Give date and vendor - correct record set returned
Give date and stock number - no records returned
Give date,vendor and stock number - correct date and vendor but returns all stock numbers

I can't figure out what's wrong, the if statement looks fine to me. I am running Crystal version 9.

Any help would be very much appreciated.

 

[M8tt]

Try this:

{@DateOriginal} = {?DateOriginal} and
(
if{?StockNumber}=&quot;&quot; and {?Vendor}=&quot;&quot;
then {IPPOITM.STOCK_NUMBER}like&quot;*&quot; and
{IPPOITM.VENDOR_NUMBER}like&quot;*&quot;

else if{?StockNumber}=&quot;&quot; and {?Vendor}<>&quot;&quot;
then{IPPOITM.STOCK_NUMBER}like&quot;*&quot; and
{IPPOITM.VENDOR_NUMBER}like{?Vendor}

else if{?StockNumber}<>&quot;&quot; and {?Vendor}=&quot;&quot;
then{IPPOITM.STOCK_NUMBER}like{?StockNumber} and
{IPPOITM.VENDOR_NUMBER}like&quot;*&quot;

else {IPPOITM.STOCK_NUMBER}like{?StockNumber} and
{IPPOITM.VENDOR_NUMBER}like{?Vendor}
)

 

[Ngolem]

Well first of all parameters should not be NULL...I have never used CR9 but other versions of CR will not allow a report to run unless a parameter has a value.

So you should give it a default value of &quot;ALL&quot; or the equivalent meaning as such for each parameter

this is important in your structure of your IF-Then since if it is possible for {?Vendor} to be null, you don't test this until much later on

I am not sure what {@DateOriginal}is...you do not define this formula...in a record selection formula doing it this way (using a formula )will slow your report down but a great margin....Why? ...because it cannot be passed to the server and will be used on the second pass through the records...It sounds to me as though it is a date range of some sort (Table.StartDate} and {Table.EndDate}...you should define this for us.

You are also repeating your criteria unnecessarily...

try this instead...I am going to assume that {IPPOITM.STOCK_NUMBER} and {IPPOITM.VENDOR_NUMBER} are string-type fields as you are using &quot;Like&quot; with them...



((Table.StartDate} in {?DateOriginal} and
{Table.EndDate}in {?DateOriginal}) and

(if{?StockNumber}<> &quot;ALL&quot; then
{IPPOITM.STOCK_NUMBER}= {?StockNumber}
else if{?StockNumber} = &quot;ALL&quot; then
True and

(If {?Vendor}<> &quot;ALL&quot; then
{IPPOITM.VENDOR_NUMBER}= {?Vendor}
else If {?Vendor} = &quot;ALL&quot; then
True

This should work better...with proper {Table.StartDate} and {Table.EndDate} dates of course.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[bluevelvet]

Hi everyone, thanks for the suggestions.

M8tt I gave yours a go and it gave me all the records for the date parameter but returned all stock numbers and vendors regardless of what I passed to these 2 parameters..

Jim the date formula is there because in the database the date is a string, I know it slows everything down having to calculate it but it is unavoidable. Anyway I gave your code a go and it didn't return anything so I'm thinking that I haven't done something with ALL that I should have, don't I need to define what all is somewhere??

blue

 

[Ngolem]

Well if the date is a string in the database then avoid using the formula and have the Date Parameter entered as a string....

In the description of the Date parameter describe the format of the date string so your user knows what to do

eg. dd/mm/yyyy

then in the default input...create a &quot;mask&quot; which further helps with the input

your mask would be 00/00/0000
this format requires numbers to replace the 0's

As far as the ALL is concerned...make this the default value of each parameter...pay attention to the case...&quot;All&quot; is different than &quot;all&quot; is different from &quot;ALL&quot;. By making the default &quot;ALL&quot; you avoid a problem of user entering the wrong case.

You could bullet-proof your formula a bit by forcing the user input into uppercase...so your formula could be as follows:

(Table.Date} = {?DateOriginal} and

(if ucase({?StockNumber}) <> &quot;ALL&quot; then
{IPPOITM.STOCK_NUMBER}= {?StockNumber}
else if ucase({?StockNumber}) = &quot;ALL&quot; then
True and

(If ucase({?Vendor}) <> &quot;ALL&quot; then
{IPPOITM.VENDOR_NUMBER}= {?Vendor}
else If ucase({?Vendor}) = &quot;ALL&quot; then
True

try that approach

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[bluevelvet]

Thanks Jim will give that a go......