Solved

Crystal Reports 2008 - code not correct

Posted on 2013-02-05
16
329 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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