if not and startswith formula

[oceana6]

Hello. I have a formula that gives me the accounted budget amount if the account code = 5419. Here's what it looks like: if {AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY} = '5419' then {AF_PERIOD_OPERATING_STATEMENT.SUM_ACCOUNTED_BUDGET}

Can anyone help me create another similar formula, that will give me the accounted_budget if the account code starts with 5, but IS NOT 5419? I am new to Crystal Reports still and can't figure it out. I've tried this and it doesn't work:

if {AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY} <> "5419" and if {AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY} startswith '5'
then {AF_PERIOD_OPERATING_STATEMENT.SUM_ACCOUNTED_BUDGET}


Thanks a bunch.

 

[kchaudhry]

You can use something like this:

if {AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY} <> "5419" and left({AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY},1)= "5"
then {AF_PERIOD_OPERATING_STATEMENT.SUM_ACCOUNTED_BUDGET}

This is based on the assumption that you account key code field is a string.

Hope this helps!


Kchaudhry

 

[oceana6]

Hi there. Thanks for your response, very much appreciated. However, your solution seems to only give me one account code that starts with 5, rather than all of them? I would like the sum_accounted_budget where all codes start with 5, except 5419.

 

[MCuthill]

oceana6,

Not sure this is what you're looking for, but give this a try:

[blue]IF (not[/blue]({AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY} = "5419")[blue]AND[/blue]
[blue]left[/blue]({AF_PERIOD_OPERATING_STATEMENT.ACCT_CODE_KEY},1)= "5")
[blue]THEN[/blue] {AF_PERIOD_OPERATING_STATEMENT.SUM_ACCOUNTED_BUDGET}

This may, or may not give the same result as the formula give by Kchaudhry above.

As a question of clarification, what is the overall structure and purpose of the report? I assume you are doing other calculations on accounts that are neither 5419 and do not start with 5?

Hope this helps,

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[MCuthill]

oceana6,

Another idea that occured to me just after I submitted my last post:

Can you post your record/group selection criteria as it may be what is limiting the accounts that are showing up.

Thanks,


Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[oceana6]

Hey Mike. Thanks for your response. I tried your formula from above, and I still get the same results. My group selection criteria is only based on the org code, so I don't think that is the problem.

 

[lbass]

kchaudry's formula should work. Where are you placing it? In what section? This is a detail level formula, not a selection formula.

-LB