Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1635
  • Last Modified:

Report Builder 3.0 Calculated Field Expression Syntax for item similar to "contains"

I have a field in my database that displays a list of numbers separated by a coma.  Example "0, 1, 2" or "1, 3, 5, 6" .  I want to create a calculated field that says if the original field contains a 1, then display the corresponding text description.  The 1 could be in any location along the series.  I've tried various combinations of Iif, Left, or Like, but these keep resulting in syntax errors.  I'm looking for help to identify the proper function and related syntax.  

Any suggestions are greatly appreciated.

Thank you,
0
jaisy99
Asked:
jaisy99
  • 2
  • 2
1 Solution
 
ValentinoVBI ConsultantCommented:
The easiest option is to prefix and suffix the string with the delimiter and combine that with the InStr function.  In your example the delimiter is a comma followed by a space.  Here's how that goes:

=IIF(InStr(", " & Fields!YourField.Value & ", ", ", 1, "), "found", "not found")

Open in new window


Note that the search term starts and ends with the delimiter.  This is crucial because otherwise you'll find 1 while it's actually 11 (for example).
0
 
jaisy99Author Commented:
Thank you for pointing me in the right direction with Instr.  I'm new to report building and did not think to go to Instr first.  I ended up going with the following syntax =IIF(InStr(Fields!CF_Str.Value,"1")>0,"Found", " ")   , but the suggested syntax was helpful as well.
0
 
jaisy99Author Commented:
What I needed.  Thanks!
0
 
ValentinoVBI ConsultantCommented:
I ended up going with the following syntax =IIF(InStr(Fields!CF_Str.Value,"1")>0,"Found", " ")   , but the suggested syntax was helpful as well.

Well, as long as your numbers don't go higher than 9 it should work fine.  Otherwise you'll run into the issue mentioned in my first post.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now