Why does this throw an error?

[cotton31]

Any ideas why this returns an error?

=Iif(Len(Fields!FailedOn.Value) > 0,Left(Fields!FailedOn.Value,Len(Fields!FailedOn.Value)-1),"")

Basically, FailedOn is a string of which questions a candidate failed on, separated by a comma. I can end up with the string Q1,Q3,Q4, - I want to remove the trailing comma, which is why I'm taking the length of the string and subtracting 1, as there is always a trailing comma. For any values of FailedOn where the length is not greater than 0, it returns #Error.

If I remove the Left(...) function and replace the true and false values with static values like "Yes" and "No", values of FailedOn where the length is not greater than 0 correctly return "No". I'm not sure why this happens though, as the Left function is in the true section of the Iif statement and values of FailedOn where the length is not greater than 0 should bypass the true part.

Any ideas? This is VB by the way.

 

[gillywibble]

=Iif?

 

[cotton31]

Thanks for your reply. IIf is the correct syntax for VB in this case, so that doesn't seem to be the problem.

As I said above, I've removed the Left(...) part, still using the IIf statement and it works fine. Thanks for your suggestion though!

Also I forgot to mention in my first post, that if the IIf statement returns true, then it works as it should do!

 

[Goofball]

Because IIF is a function and not a conditional expression operator.

That means that both the true and false case will be evaluated, passed to the function and the correct one is then returned. In your case, Left(Fields!FailedOn.Value,Len(Fields!FailedOn.V alue)-1) is evaluated before the function determines which argument to return, and thus fails if the string is empty. You need an IF statement to do this assignment, since that expression cannot be safely evaluated in the false case.

IIF does not behave like the C style ? expression, or VB.Net If() expression, where only the correct output is evaluated. Those are short circuiting, function calls are not like that outside functional programming (where you cannot venture in classic VB/VBScript).

 

[cotton31]

snip...

Thanks Goofball that makes sense! Unfortunately I'm quite limited in terms of what I can use (I'm using the Layout section of SQL Server Reporting Services BIDS tool, which is a component of Visual Studio) and the only IF style function it seems to offer is IIf - correct me if I'm wrong...

Can you see another way I can strip the last character? The IIf was only there to test whether the string contained anything or not in the first place anywauy!

=Iif?

Apologies if Goofball's answer is what you meant by this - I thought you were referring to it as a typo.

 

[cotton31]

Actually I've just sorted it myself by moving the Iif to decide the number of characters to subtract from the end of the string based on the length of the string:

=Left(Fields!FailedOn.Value,Len(Fields!FailedOn.Value)-Iif(Len(Fields!FailedOn.Value) = 0,0,1))

Not sure if it's the best or most efficient way, but it works for me.