Solved

Crystal Reports 2008 - code not correct

Posted on 2013-02-05
16
327 Views
Last Modified: 2013-02-05
Hi and thanks,

I have this code:
{TEMP_PWC_Report_SP_MAIN.TaxYear} = ToNumber({?WorkingYear}) and
{TEMP_PWC_Report_SP_CHARACTERISTICS.YearBuilt} = ToNumber({?YearBuilt}) and
not ({@Ratio} in ToNumber({?StartRatio}) to ToNumber({?EndRatio})) and
{@Asmt} > ToNumber({?RecostValue}) and
{@SaleDate} in {?StartDate} to {?EndDate} and
(
If {?TaxType} = True Then
not({TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"])
Else
True
)

For some reason it is not working correctly and to narrow it down it looks like it is this part?
(
If {?TaxType} = True Then
not({TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"])
Else
True
)

of the code.

Please help and thanks
0
Comment
Question by:Amour22015
  • 10
  • 5
16 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 200 total points
ID: 38855346
If {?TaxType} = True AND ???
not({TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"])
Else
True


what are you intending this to do?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38855368
Do you mean that it is producing the wrong answer or that you are getting an error message?

I take it that Tax_type is definitely a text field, not a number.
0
 

Author Comment

by:Amour22015
ID: 38855387
Ok this {?Taxtype} is a boolean field.

So If the user selects True then
not({TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"])
Else
True

thanks
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 77

Expert Comment

by:peter57r
ID: 38855405
I think the point that Lowfatspread was making is that you have not posted all the code and so we can't work out what is supposed to be happening.  My questions above still stand.
0
 

Author Comment

by:Amour22015
ID: 38855417
Yes it is producing the wrong answer

Tax_Type (varchar(15), null)

Thanks
0
 

Author Comment

by:Amour22015
ID: 38855449
This is all the code:
{TEMP_PWC_Report_SP_MAIN.TaxYear} = ToNumber({?WorkingYear}) and
{TEMP_PWC_Report_SP_CHARACTERISTICS.YearBuilt} = ToNumber({?YearBuilt}) and
not ({@Ratio} in ToNumber({?StartRatio}) to ToNumber({?EndRatio})) and
{@Asmt} > ToNumber({?RecostValue}) and
{@SaleDate} in {?StartDate} to {?EndDate} and
(
If {?TaxType} = True Then
not({TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"])
Else
True
)

{@Asmt} = (Land + Improvements)
{@Ratio} = {@Asmt}/{SaleAmount}

Thanks
0
 
LVL 77

Accepted Solution

by:
peter57r earned 300 total points
ID: 38855521
Can't really see anything wrong...
Can Tax_type ever be Null?  I take it you are getting wrong answers when it is not null?

Try doing the same thing with...

If {?TaxType} = True  and {TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"] Then
False
Else
True

As a testing measure you could add a final command to show the result of this test on the report
..
;
{TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"]
0
 

Author Comment

by:Amour22015
ID: 38855578
I tried your code and still regardless if I select True or False I get the same amount of records.

Thanks.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38855631
So if you select False, you expect to see records with Tax_type 2,3,4 in the report.

If you select True you don't expect to see records with Tax_type 2,3,4.

Am I understanding it correctly?

Is tax _type in the report - if not add it in temporaraily so that you can see what is going on.
0
 

Author Comment

by:Amour22015
ID: 38855659
yes in either case the tax_Type = 1

Thanks
0
 

Author Comment

by:Amour22015
ID: 38855683
If I do a SQL Query on this:
Where Tax_Type in ('2','3','4')

I get 5365 records

If:
Where Tax_Type = '1'

I get a real large amount of records.

Also I checked and Tax_Type has a 1, 2, 3, 4  in the fields

Thanks
0
 

Author Comment

by:Amour22015
ID: 38855710
looks like there is something in that code that is ignoring the section:
(
If {?TaxType} = True  and {TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2", "3", "4"] Then
False
Else
True
)

maybe I need to place a ";" somewhere?

Thanks
0
 

Author Comment

by:Amour22015
ID: 38855832
I tried:

({?TaxType} = False  and {TEMP_PWC_Report_SP_MAIN.Tax_Type} = "1"  Or
{?TaxType} =True  and {TEMP_PWC_Report_SP_MAIN.Tax_Type} in ["2","3","4"])

That seems to word better

Thanks
0
 

Author Comment

by:Amour22015
ID: 38855837
Sorry, type mistake I meant to say:
That seems to work better...
0
 

Author Closing Comment

by:Amour22015
ID: 38855917
Thanks for the help
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38856226
Well if it works then fine.

I nearly suggested the command structure you've now adopted, but I really couldn't see how it was logically different to what you were already using.  
It's hard to see why an explicit test for 1 makes a difference , but as I say, all that matters is that it works.

It is interesting from one aspect - MS Access often has problems using a Not In() structure and it is then necessary to find alternatives. Perhaps Not In() is just a bad idea all round.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question