Solved

If ... Then Select... Else Syntax Problems

Posted on 2004-03-23
7
601 Views
Last Modified: 2008-02-26
I have a formula field that I had to change around a bit and it no longer gives me the results I want.  My old formula was comprised of this:

-------

if {JC1_JobMaster.Comment} like "*CORNER*" or {JC1_JobMaster.Comment} like "*DETACHED*" then
select {JC1_JobMaster.SortField}
       
        case "SADDLE M" : 140
        case "SUGAR" :  345
        case "SADDLEROCK" : 80
        case "SAVANNAH" : 43
        case "PARKFIELD" : 71.85

        default: 0

--------

On the report, all values for this field would show up as 0, or as one of the numbers from the case statement.  An important thing to know is that the line for default of 0, is not why 0 shows up.  Meaning, that if the condition is false, it still shows up as a 0 on the report.

Now when I changed my formula to this:

--------

if {JC_90_UDF_Job_Cost_Header.Trcorner} like "Y" then
select {JC1_JobMaster.SortField}
       
        case "SADDLE M" : 140
        case "SUGAR" :  345
        case "SADDLEROCK" : 80
        case "SAVANNAH" : 43
        case "PARKFIELD" : 71.85

        default: 0

---------

I no longer get a 0 showing up when the if statement is false.  I just get a blank field, and I need it to be 0.  I know it should probably be something like this:

If (condition) then
select

...

else
0

But I can't figure out the syntax.

Any help would be appreciated, and I'm curious to know if anyone can explain why the 0 values stopped showing up.

Thanks!
0
Comment
Question by:Mourdekai
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 10659586
Hello Mourdekai,

it shouldbe something like this:
 
 If (condition) then
 (
select
 
 ...
 )
 else
 0



Pete
0
 
LVL 8

Author Comment

by:Mourdekai
ID: 10659621
Pete,

if {JC_90_UDF_Job_Cost_Header.Trcorner} like "Y" then
(select {JC1_JobMaster.SortField}
       
        case "SADDLE M" : 270
        case "SUGAR" :  215
        case "SADDLEROCK" : 280
        case "SAVANNAH" : 430
        case "PARKFIELD" : 701.85

        default: 0;)
else
0


Does NOT work, any other suggestions?
0
 
LVL 8

Author Comment

by:Mourdekai
ID: 10659730
I figured it out, this is what I needed before my if

if isnull({JC_90_UDF_Job_Cost_Header.Trcorner}) then 0
else
if {JC_90_UDF_Job_Cost_Header.Trcorner} like "Y" then
select {JC1_JobMaster.SortField}
...
...

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 28

Expert Comment

by:bdreed35
ID: 10659778
Try this:

if instr({JC_90_UDF_Job_Cost_Header.Trcorner},"Y")>0 then
(select {JC1_JobMaster.SortField}
       
        case "SADDLE M" : 270
        case "SUGAR" :  215
        case "SADDLEROCK" : 280
        case "SAVANNAH" : 430
        case "PARKFIELD" : 701.85

        default: 0;)
else
0
0
 
LVL 8

Author Comment

by:Mourdekai
ID: 10659876
bdreed35, thanks for trying but that doesn't work either.  See my above post for the solution I found.
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 10694322
Closed, 172 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal report running 3 42
cross tab crystal reports - filter a row 7 48
Basic Crystal Report not showing up on ASP.NET page 4 67
Crystal Reports 9 and Subreports 3 52
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now