Simple if statement won't work 1

[Skip1961]

I know this is terribly basic, but I can't get by it.

I have created a formula named @citystzip. It is constructed of the three obvious fields: {tsworking.city} + ", " + {TSWorking.State} + " "+ {TSWorking.Zip}

But now I want the format to be different based on a country code in a field in the same table. comm2, a text field.

if tonumber({TSWorking.Comm2}) > 7 and tonumber({TSWorking.Comm2}) < 19 then
{TSworking.city} + " " + {TSWorking.State} + " "+ {TSWorking.Zip}
else
{TSworking.city} + ", " + {TSWorking.State} + " "+ {TSWorking.Zip}

Simple, eh? It returns nothing when I call @citystzip. I get no errors on Check.

Thanks for any help.

 

[pmax9999]

Could any of the 3 fields ({TSworking.city}, {TSWorking.State} or {TSWorking.Zip}) be null? If so, the formula would fail.

Include a test for nulls as the first step of the formula.

Hope this helps


Cheers
Pete

 

[hilfy]

You also need to test {TSWorking.Comm2} for null. For more information about issues with nulls, see my blog post here:

http://sapbiblog.com/2013/03/25/what-is-null-and-why-is-it-important-for-crystal-reports/

Because checking for nulls can require a very complicated "if" statement when you're trying to do it all in one formula, I usually create separate formulas for city, state, and zip and use those in the formula that puts them together. These usually look something like this:

{@City}
If IsNull({TSworking.city}) then "" else {TSworking.city}

{@State}
If IsNull({TSworking.state}) then ""
else If {@City} = "" then {TSworking.state} else ", " +{TSworking.state}

{@Zip}
If IsNull({TSworking.zip}) then "" else {TSworking.zip}

{@citystzip} would then be something like this:

if IsNull({TSWorking.Comm2}) then <whatever format you need for a null country code>
else
if tonumber({TSWorking.Comm2}) > 7 and tonumber({TSWorking.Comm2}) < 19 then
{TSworking.city} + " " + {TSWorking.State} + " "+ {TSWorking.Zip}
else
{TSworking.city} + ", " + {TSWorking.State} + " "+ {TSWorking.Zip}

-Dell



DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[Skip1961]

Nulls, if any, don't seem to bother it. The @citystzip formula works just fine when I place the formula on the report like a database field and call it directly. "Chicago, IL 34567".

It fails when I put the " if > 7 and < 19 " language in front of it. Seems like I should get the "if" or the "else" result, but the if statement causes no output at all.

 

[hilfy]

Is it possible for the Comm2 field to be null? Here's another thing to try:

if not IsNull({TSWorking.Comm2}) and {TSWorking.Comm2} in ['8','9','10','11','12','13','14','15','16','17','18'] then
{TSworking.city} + " " + {TSWorking.State} + " "+ {TSWorking.Zip}
else
{TSworking.city} + ", " + {TSWorking.State} + " "+ {TSWorking.Zip}

You could also temporarily put {TSWorking.Comm2} on your report to see what the value is as your report is processing.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[AndyMc]

if val({TSWorking.Comm2}) > 7 and val({TSWorking.Comm2}) < 19 then

I don't know if this will address your situation, but I find I have much better luck using val()
function instead of tonumber() function.

 

[Skip1961]

Hilfy, exactly correct. Thank you. A lot!