IF/Else within the Formula Editor STILL being ignored

[ZanX]

This is the code:

{sa_bale_class.sale_no} = {?sale_no_in} and
{sa_bale_class.current_division_no} <> 35 and
{sa_bale_class.current_division_no} <> 38 and
if {?drop-no-in} > 0 then
{sa_bale_class.sale_drop_no} = {?drop-no-in}
Else
{sa_bale_class.sale_drop_no}>0


Crystal is being CALLED from ASP page (this code is not inside the ASP). I have tried Crystal syntax...but that is not an issue here. I have tried () everywhere. Thanks for the previous replies...any help send this WAY!!!

 

[kenhamady]

Is this in the selection formula?

An If-Then in the selection formula should always be in parenthesis unless it is alone in the formula.

Does this formula work when you run the report from the report designer?

How does it fail? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[balves]

Assuming this is a selection formula for the report....

Crystal has to translate your selection formula into SQL it can send to SQL Server, Oracle. What is it supposed to do with your IF statement? Nothing, because it has nothing to do with selecting records. It doesn't belong in the selection formula. It belongs somewhere in the report, in the detail section or whatever.

This part of the statement is not legal anywhere:

Else
{sa_bale_class.sale_drop_no}>0

 

[ZanX]

Ok. Two good points. To KenHamady: I am not sure what the report designer is. If I leave out the ELSE and just include the IF...Crystal ignores the IF and processes the line after the IF ({sa_bale_class.sale_drop_no} = {?drop-no-in})...and I can get a result...as long as I am not trying to generate the report with a sale_drop_no of 0.

To balves: The IF/ELSE is critical for my SQL query. The users must be able to Select for a Sale_no and one sale_drop_no of the Sale_no...or all the sale_drop_no(s) for the Sale_no...hence the >0 code. Any sale_drop_no that exists will be >0. It works in ASP, but not in Crystal.

You both have me thinking...please continue to try and help and I am sure we can figure this out...I am new to Crystal, so I am not totally sure of the rules. Thanks!!!

 

[balves]

What does the user type in for {?drop-no-in} if they want all the records? 0?

If so:

{sa_bale_class.sale_no} = {?sale_no_in} and
{sa_bale_class.current_division_no} <> 35 and
{sa_bale_class.current_division_no} <> 38 and

(({?drop-no-in} > 0 AND {sa_bale_class.sale_drop_no} = {?drop-no-in})
OR
({?drop-no-in} = 0 AND {sa_bale_class.sale_drop_no}>0))

 

[kenhamady]

The report designer is Crystal Reports itself, as opposed to running the report from within an application. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[ZanX]

We are getting close!!! Ken...I understand now...and no, I am not getting the exact results I need.

balves: Ok...this is the WHERE that is produced from your code if the user enters a sale_drop_no of...say...206:


sa_bale_class.&quot;sale_no&quot; = 73333. AND
sa_bale_class.&quot;current_division_no&quot; <> 35 AND
sa_bale_class.&quot;current_division_no&quot; <> 38 AND
(sa_bale_class.&quot;sale_drop_no&quot; = 206. OR
sa_bale_class.&quot;sale_drop_no&quot; > 0.)

I need it to be just the = 206. Now, to answer old question further: What does the user type in for {?drop-no-in} if they want all the records? 0? Yes...they type in a 0. This is the result of the code you provided with a 0 entered:

sa_bale_class.&quot;sale_no&quot; = 73333. AND
sa_bale_class.&quot;current_division_no&quot; <> 35 AND
sa_bale_class.&quot;current_division_no&quot; <> 38 AND
(sa_bale_class.&quot;sale_drop_no&quot; = 0. OR
sa_bale_class.&quot;sale_drop_no&quot; > 0.)

This part will work!!! I just need to get the other part going, which is what I have had probles with. That is why I was using the IF statements...almost there...I can feel it!!!

 

[balves]

Did you make sure to put the right number of parentheses:

...
((a AND B) OR (c AND D))

 

[ZanX]

Yep...both parenthesis are there...it still uses the = and the > and I just need it to pick one based on a number > 0...or = to 0...thats it...but it wont select a specific scenario...

 

[balves]

I tried a little test and ((a AND b) OR (c AND d)) seems to work for me. It appears to get the right data.

On the downside, Crystal will bring ALL of the records back to the PC to apply the WHERE clause. How do you know this? Go to the menu Database and pick SHOW SQL QUERY. There is no WHERE clause. So Crystal just selects from the table and applies the filter back at the PC. If you have a million rows in your table, this is not a good thing.

 

[kenhamady]

I am sorry, I am getting lost. Could you post the actual formula that is the closest to working, and explain what it is doing wrong?

Is the field {sa_bale_class.sale_drop_no} ever NULL? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[ZanX]

balves: When I do &quot;Show SQL query&quot; I actually do show a WHERE...which is the code I showed you from above with the two different scenarios...that is the code that shows up in the &quot;SHOW SQL Query&quot; which is produced from the code we have created in the Formula Editor...so we are getting different results.

To both (balves & KenHamady): Crystal is being called from an ASP page...the user picks a sale_no & and a sale_drop_no...the sale_drop_no will never be null as far as the user is concerned...when the user enters a 0 for sale_drop_no...we are selecting every single sale_drop_no for one sale_no (there can be multiple sale_drop_no(s) for one sale_no). The user needs to be able to do this. But, the user must be able to select a specific sale_drop_no also...instead of all the sale_drop_no(s) for a particular sale_no. I hope this is becoming more clear...I am eager to figure this out.

KenHamady: I will post the &quot;best&quot; code for you on 09/04 when I get back to work...

THANK YOU BOTH!!!

 

[Ngolem]

Hi ZanX;

I have been following this thread and think I understand your problem...

Let me restate what you are trying to do,,,,you want the user to select one or ALL sale_drop_no, right!!

I have done this often and this is my approach which you may find useful.

The parameter, ?sale_no_in, I set as a string parameter with instructions to the user to enter a single sale_drop_no or 'ALL' for all sale_drop_no

then I create a formula called &quot;Select - sale_drop_no&quot;

>***********************cut here**************<
Select - sale_drop_no

BeforeReadingRecords;
numberVar upperRange;
numberVar lowerRange;

if not numerictext({?sale_no_in}) then //ie. 'ALL' selected
(lowerRange := 0;
upperRange := 9999999 // or suitable high number
else
(lowerRange := tonumber({?sale_no_in});
upperRange := tonumber({?sale_no_in})

>******************end***********************

Then your selection formula would look like

numberVar upperRange;
numberVar lowerRange;

{sa_bale_class.sale_no} = {?sale_no_in} and
{sa_bale_class.current_division_no} <> 35 and
{sa_bale_class.current_division_no} <> 38 and{sa_bale_class.sale_drop_no} in lowerRange to upperRange;

NOTE: you will have to do something about the line

{sa_bale_class.sale_no} = {?sale_no_in} and

perhaps creating a 3rd variable in the select formula to replace {?sale_no_in} but I think this approach will help you

regards Jim

 

[Guest_imported]

First of all, Yes I want the user to be able to select one sale_drop_no or All sale_drop_no for ONE sale_no...which is done by entering a 0 (for ALL) in the sale_drop_no text box.

I am a little confused Ngolem. Will I put all of the code you provided in the Formula Editor; or will I use the Select - sale_drop_no in the formula editor? I am just getting confused where everything needs to be placed.

Also, I still do not understand why the code in the very first thread will not work. Thank you for following up this thread. I am pretty sure I understand what you are doing with the code, but I am not sure where to put all of it...

 

[FoxG]

Hi,

Try ...

{sa_bale_class.sale_no} = {?sale_no_in}
and {sa_bale_class.current_division_no} <> 35
and {sa_bale_class.current_division_no} <> 38
and (if {?drop-no-in} > 0 then
{sa_bale_class.sale_drop_no} = {?drop-no-in}
Else
{sa_bale_class.sale_drop_no}>0
)

Geoff

 

[ZanX]

Ngolem: AddCrystalReportParm 2, intDrop_no, 7

Do I need to change the intDrop_no to something else, so that crystal expects a string instead of a number?

When I try to use the Select - Sale_drop_no formula that you gave me, I get an error on the first line: String is expected. Is that because I am passing the sale_drop_no in as a number? Could you guide me a little more, and then I think I will be able to get it...

 

[Ngolem]

Hi Zanx;

{Select - Sale_drop_no} is a regular formula

NOTE: the BeforeReadingRecords in {Select - Sale_drop_no}

This means this formula is evaluated before the record selection formula so the result is ready...otherwise the records will be processed twice

yes,I am expecting that {?sale_no_in} is a string since usually I have the user select &quot;ALL&quot; if they want all records...selecting a numeric zero for all records is confusing to me. Also a value of zero could be a valid result...not a FLAG...converting it to a string and using &quot;ALL&quot; for all records is better

the following is in your record select

numberVar upperRange;
numberVar lowerRange;

{sa_bale_class.sale_no} = {?sale_no_in} and
{sa_bale_class.current_division_no} <> 35 and
{sa_bale_class.current_division_no} <> 38 and
{sa_bale_class.sale_drop_no} in lowerRange to upperRange;

As I pointed out earlier you will have to do something about the reference to {?sale_no_in} above...it looks like to me you are using one parameter for 2 purposes

 

[ZanX]

Sale_no_in & sale_drop_in are two different, but important, parameters.

I entered this for sale_drop_no:

BeforeReadingRecords;
numberVar upperRange;
numberVar lowerRange;

if not numerictext({?sale_no_in}) then //ie. 'ALL' selected
(lowerRange := 0;
upperRange := 9999999 // or suitable high number
else
(lowerRange := tonumber({?sale_no_in});
upperRange := tonumber({?sale_no_in})


The first problem I had was: Cannot perform at this time...pointing to BeforeReadingRecords;...I took it out.
Then, it said: String expected here...pointing to numberVar upperRange;...so I did Edit Field Object for sale_drop_no and changed it from Number to String...

And then I entered the other code that you gave me in the selection formula. It is not pulling in any information.

This is what the query looks like (from the WHERE down):

WHERE
sa_bale_class.&quot;sale_no&quot; = 73333. AND
sa_bale_class.&quot;current_division_no&quot; <> 35 AND
sa_bale_class.&quot;current_division_no&quot; <> 38

This should at least work...although it is not right, but I still get nothing at all....???...any thoughts///

 

[Ngolem]

BeforeReadingRecords;
numberVar upperRange;
numberVar lowerRange;

if not numerictext({?sale_no_in}) then //ie. 'ALL' selected
(lowerRange := 0;
upperRange := 9999999 // or suitable high number
else
(lowerRange := tonumber({?sale_no_in});
upperRange := tonumber({?sale_no_in})


place this formula in the first section of the Report Header (if you have more than 1 section)and suppress it

 

[billybragg]

OK, I'll try.

If I understand correctly, this is much simpler than we are making it.

I run a report in which the user is prompted to enter a month, company, department and product, all represented by numbers. However, they may choose ALL companies, ALL departments, and/or ALL products. I did something similar to what Ngolem suggested, making one of the selections for those parameters &quot;ALL&quot;. Then in the SELECT statement, IF {?Company} = &quot;ALL&quot; THEN company_no <> &quot;&quot;, which pulls records for all companies. Does that make sense? I hope it helps.

Here's the actual SELECT statement:

(If {?Product Code}=&quot;ALL&quot; Then {open_years.oprod}<>&quot;&quot; Else
{open_years.oprod}={?Product Code}) and
(If {?Department}=&quot;ALL&quot; Then {open_years.odept}<>&quot;&quot; Else
{open_years.odept}={?Department}) and
(If {?Company}=&quot;CBN&quot; Then {open_years.obus}=[&quot;CBN&quot;] Else
If {?Company}=&quot;WIN&quot; Then {open_years.obus}<>[&quot;CBN&quot;] Else
If {?Company}=&quot;WNP&quot; Then {open_years.obus}=[&quot;WIN&quot;] Else
If {?Company}=&quot;CHJ&quot; Then {open_years.obus}=[&quot;CHJ&quot;] Else
If {?Company}=&quot;DBR&quot; Then {open_years.obus}=[&quot;DBR&quot;] Else
If {?Company}=&quot;EIN&quot; Then {open_years.obus}=[&quot;EIN&quot;] Else
If {?Company}=&quot;LHO&quot; Then {open_years.obus}=[&quot;LHO&quot;] Else
If {?Company}=&quot;SVN&quot; Then {open_years.obus}=[&quot;SVN&quot;] Else
If {?Company}=&quot;WTN&quot; Then {open_years.obus}=[&quot;WTN&quot;] Else
{open_years.obus}<>&quot;&quot and
{open_years.oacct_type} in [3.00, 4.00]