0

I'm trying to edit an expression on an existing form we use. The field in question currently only has a simple =SWITCH expression. I want to add an additional expression either nested in the SWITCH or any other way really, that will check an additional field value from the same DataSet1, and print out additional T.O.S info based on the commodity below the existing text. Currently, the expression looks like this:

=SWITCH(First(Fields!Origin_country, "DataSet1") = "USA",
"***PRINTS US VERSION OF TERMS OF SERVICE AGREEMENT***"
& vbCrLf & 
"More text goes here...",

--Need to add nested expression to check a different field value  from dataset 1.

First(Fields!Origin_Country, "DataSet1") = "CAN",
"***PRINTS CDN VERSION OF T.O.S***"
& vbCrLf &
"More text goes here..."


--Need to add nested expression to check a different field value  from dataset 1.

)  -- Closing bracket from =SWITCH

I tried adding the additional Field values within the existing =Switch, but i get the error: "Paragraphs[0].TextRuns[0]’ contains an error: [BC30201] Expression expected." Tried adding an IIF statement within the =SWITCH but i get this error instead: "The Value expression for the text box ‘textbox28’ refers directly to the field ‘cmd_code’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope. "

The Field Value i want to check include 9 commodity codes which i can reduce to 2 as LIKE "PRO*" and LIKE "SEAFOO*" (Produce and seafood). They all fall under produce commodity so they will all have the same True result printed out below the existing T.O.S; 1 with US T.O.S and the other Canadian...

Thank you!

samalkobi
  • 77
  • 1
  • 10

2 Answers2

1

From This what I can tell is your code has one too many commas.

=SWITCH(First(Fields!Origin_country, "DataSet1") = "USA",
"***PRINTS US VERSION OF TERMS OF SERVICE AGREEMENT***",
& vbCrLf & 
"More text goes here...",

where it should be 

=SWITCH(First(Fields!Origin_country, "DataSet1") = "USA",
"***PRINTS US VERSION OF TERMS OF SERVICE AGREEMENT***" (take this comma off)
& vbCrLf & 
"More text goes here...",
First(Fields!Origin_Country, "DataSet1") = "CAN",
"***PRINTS CDN VERSION OF T.O.S***" (Take Comma Off)
& vbCrLf &
"More text goes here..."

Another option also would be to set the String for prints the Terms of service in a sql query and pull it in a dataset so you can say 

IIF First(Fields!Origin_Country, "DataSet1") = "CAN", Fields!USATOS.Value...
Community
  • 1
  • 1
NewGuy
  • 1,020
  • 1
  • 9
  • 24
  • Hey,Sorry, those commas were my mistake in this post, they're not in the original expression. – samalkobi Feb 02 '16 at 15:30
  • 1
    Hmm..I did infact have an extra comma after the addtional line break i added...Removed the comma after & vbCrLf & and before the IIF statement and it worked! Only issue now is that the form got pushed to the left slightly so the left border of the text boxes is cut off...lol – samalkobi Feb 02 '16 at 15:45
0

So i wasn't that far off and newGuy pointed out it could just be a misplaced comma, which it was ( Had a comma preceding the IIF statement)

Expression now looks like this and it worked. Slight issue with the text field positions, but i fixed that by just moving the margin a couple pixels.

=SWITCH(First(Fields!Origin_country, "DataSet1") like "USA",
"***USA Version of TOS****"
& vbCrLf & "More text Here"
&vbCrLf &    /*Had a comma on this line i had to remove... */
IIF(First(Fields!cmd_code.value, "DataSet1") like "PRO*", "USA PRODUCE TEXT",
 IIF(First(Fields!cmd_code.value, "DataSet1") like "SEAFOO*", "USA PRODUCE TEXT", " " )
     ),

First(Fields!Origin_country, "DataSet1") like "CAN",
"***CANADIAN Version of TOS****"
& vbCrLf & "More text Here"
&vbCrLf &
IIF(First(Fields!cmd_code.value, "DataSet1") like "PRO*", "CDN PRODUCE TEXT",
  IIF(First(Fields!cmd_code.value, "DataSet1") like "SEAFOO*", "CDN PRODUCE TEXT", " " )
  )
)
samalkobi
  • 77
  • 1
  • 10