IIF Function not evaluating correctly

[sboyle1]

Does anyone have any idea why the following formula doesn't work? I'm trying to return the last 6 numeric characters from a phone number. I know there are other ways to do it, but am not sure why the IIF statement isn't evaluating correctly. It seems to return "true" regardless of whether the character is numeric or not. The same example works fine with an If...Then...Else instead of an IIF.


Local NumberVar i:=1;
Local StringVar PhoneVar;

While i<=length({Customer.Phone}) do
(
IIF(IsNumeric({Customer.Phone}), PhoneVar:=PhoneVar+{Customer.Phone}, PhoneVar);
i:=i+1
);
Right(PhoneVar,6)


Thanks in Advance!

 

[kenhamady]

Your condition is:

IsNumeric({Customer.Phone})

Using the entire field each time. Did you mean to test one digit at a time? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[sboyle1]

Yes. I'm using the while statement to loop through the phone number field and parse out any non-numeric digits. The final end product is an account number based on the last 6 numeric (not &quot;-&quot;, &quot;(&quot;, or space) digits of the phone number.

The big question is why the IIF statement doesn't evaluate correctly. For example, if I have a phone number such as: 123-456-7890, the IIF statement evaluates IsNumeric as true even when the character it is looking at is &quot;-&quot;. The IIF works fine when not in a loop, and an If...Then...Else works fine in the loop. Just a curiosity question about IIF, really.

 

[kenhamady]

You missed my point. You are not telling it anywhere to evaluate only one character. You are evaluating the entire field each time. And, when you evaluate a string of numeric characters, it ignores dashes between numbers.

You probably meant to use:

IsNumeric({Customer.Phone} )

Which would check each digit in turn.
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[sboyle1]

My apologies, I missed an item in my formula when I posted it. This still doesn't work

Local NumberVar i:=1;
Local StringVar PhoneVar;

While i<=length({Customer.Phone}) do
(
IIF(IsNumeric({Customer.Phone}), PhoneVar:=PhoneVar+{Customer.Phone}, PhoneVar);
i:=i+1
);
Right(PhoneVar,6)

 

[kenhamady]

You are still missing my point. Your condition checks the entire field on every iteration, and therefore will return the same result on every iteration (All TRUE or all FALSE):

IsNumeric({Customer.Phone})

You have to change this line to check one position in the field, not the whole field. Probably using the position you are on which is the 'i' value, as I showed in my list post. Why not try my condition and tell me exactly what happens. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[sboyle1]

It looks like our confusion is due to the formatting on this site. Squarebrakcet-i-squarebracket is being interpreted as italic. My posts, and it looks like yours, drop the square brackets and anything in between them. Let's try this again:

Local NumberVar intvar:=1;
Local StringVar PhoneVar;

While intvar<=length({Customer.Phone}) do
(
IIF(IsNumeric({Customer.Phone}[intvar]), PhoneVar:=PhoneVar+{Customer.Phone}[intvar], PhoneVar);
intvar:=intvar+1
);
Right(PhoneVar,6)

 

[kenhamady]

OK, sorry about that. I don't use the format characters so I didn't even think of that.

I get the same result as you and I don't see any reason why, although it may still be something in the syntax. I may play around with it some more because the condition returns a false appropriately, it just goes ahead and makes the assignment anyway.

Is there a reason why you can't use the If-then-else structure that works? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[sboyle1]

Nope. I was teaching a Crystal Advanced class, and one of my students came up with this problem, so I thought I'd see if there was an answer to why it's not working. There's a bunch of different ways to do the same thing (.e., if...then...else), but if the IIF statement doesn't evualate this correctly, maybe it has other bugs as well, and should be avoided.

Thanks for all of your help.

 

[kenhamady]

I just avoid it because I think the IF-Then-Else is easier to follow, especially when nesting. The IIF was only added recently and may still have some bugs. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)