Solved

Crystal Reports 2008 - code not correct

Posted on 2013-02-05
16
330 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 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