Nested IIf statement syntax
-
Hello:
I should think this would be relatively easy, but I apparently keep screwing it up!
I have two fields, Phone1 and Phone2. I want to display Phone1 in my SSRS textbox unless Phone1 is blank, in which case I want to display Phone2. So basically I want the textbox to be =IIf(Fields!Phone1.Value="",Fields!Phone2.Value,Fields!Phone1.Value). That works fine, but I need to nest another two IIf statements in this statement, and that's where I'm running into trouble.
The Phone1 and Phone2 fields have prefixes of either H: (for home) or W: (for work), followed by the phone number (e.g., H:607-772-4783). I want to display the number without the prefix, but I can't use the RIGHT function because the phone numbers are of varying lengths (some have parentheses around the area code, some do not; some use dashes, some do not; etc.).
I want to use the REPLACE function to remove the prefix like this (using Phone1 as an example):
IIf(Fields!Phone1.Value startswith "H:",
REPLACE(Fields!Phone1.Value,"H:",""),
REPLACE(Fields!Phone1.Value,"W:",""))
I tried to combine the IIf statements as follows:
IIf(Fields!Phone1.Value="",
IIf(Fields!Phone2.Value startswith "H:",
REPLACE(Fields!Phone2.Value,"H:",""),
REPLACE(Fields!Phone2.Value,"W:","")),
IIf(Fields!Phone1.Value startswith "H:",
REPLACE(Fields!Phone1.Value,"H:",""),
REPLACE(Fields!Phone1.Value,"W:","")))
I keep getting an error saying "Argument not specified for parameter 'Truepart' of public function 'IIf...'" I've tried rearranging the parentheses numerous ways, and I keep getting the same error. Does anyone have any clue what's wrong with my syntax?
Thanks in advance!
-
If you want to check the characters at the start of a string, you should use the LEFT function. "startswith" is not part of the syntax of the IIF statement.
e.g.
IIf(Fields!Phone1.Value="",
IIf(LEFT (Fields!Phone2.Value, 2) ="H:",
REPLACE(Fields!Phone2.Value,"H:",""),
REPLACE(Fields!Phone2.Value,"W:","")),
IIf(LEFT(Fields!Phone1.Value, 2) = "H:",
REPLACE(Fields!Phone1.Value,"H:",""),
REPLACE(Fields!Phone1.Value,"W:","")))
-
How about:
IIf(Fields!Phone1.Value="", Fields!Phone2.Value.Substring(2), Fields!Phone1.Value.Substring(2))The parameter passed to substring is a 0 based postion in the array to start at. Thus Substring(2) returns everything starting with the 3rd character. I am assuming that every phone number has the prefix.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thanks so much to you both! Happycat, your code works perfectly! Jack, I tried yours and got the error "startIndex cannot be larger than length of string." Did I do something wrong?
-
No you did not do anything wrong. I had forgotten how the IIF statement is evaluated. I think it is evaluating the Substring for both the If and the Else so the empty phone numbers are causing the issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply