We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Mourdekai
Mourdekai asked
on
Medium Priority
643 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!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hello Mourdekai,

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



Pete

Author

Commented:
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?

Author

Commented:
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}
...
...

Top Expert 2004

Commented:
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

Author

Commented:
bdreed35, thanks for trying but that doesn't work either.  See my above post for the solution I found.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.