Solved

Crystal Reports 2008 - code not correct

Posted on 2013-02-05
16
328 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
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.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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