IIF and Like?

[jfredel via AccessMonster.com]

I need to create a indicator field that will look at a field and see if it
has a certain code in it and then return a Y or N.

I was thinking that I could use an IIF to look at the field and if it
contains a "C" or "P" or and "S" (using Like function?) then the field would
be Y and if it didnt contain any of those values it would return a N.

I use some assistance on the IIF syntax.

Very much appreciated.

Jeff

 

[Guest]

Try

IIf([FieldName] Like "*C*" or [FieldName] Like "*P*" Or [FieldName] Like
"*S*","Y","N")

 

[Allen Browne]

Create a query using your table.
Type an expression like this into a fresh column in the Field row:
CPS: (([Field1] Like "*C*") OR ([Field1] Like "*P*") OR ([Field1] Like
"*S*"))

This calculated field will contain:
- True if the letter C, P, or S is found anywhere in Field1;
- False if none of those letters are found in the field;
- Null if Field1 is null.

In the query, True will display as -1, and False as 0. Use the Format
property of the field if you want it displayed as Yes/No. Alternatively, you
can create a form or report and bind this calculated field to a check box.

 

[Guest]

Jeff,

By "contains", do you mean that you have a field that is a single character
long and you want to check to see whether the value of that field is equal to
a "C", "P", or "S"? IF so, you could do:

IIF([yourFieldName Like "[CPS]", True, False)

If what you mean is that you want to check to see whether the text in that
field "contains" one of those letters, you could use:

IIF([yourFieldName] Like "*[CPS]*", True, False)

HTH
Dale

 

[Guest]

IIf(FieldName IN("C","P","S"), "Y", "N")

 

[Marshall Barton]

I need to create a indicator field that will look at a field and see if it
has a certain code in it and then return a Y or N.

I was thinking that I could use an IIF to look at the field and if it
contains a "C" or "P" or and "S" (using Like function?) then the field would
be Y and if it didnt contain any of those values it would return a N.

Does this do what you want?

=IIf([indicator field] Like "*[CPS]*", "Y", "N")