IIF with instr

[yoili]

would like to make an expression in an Access report, that if the field contains a space and the word "is" it should return one text and if it doesn't contain it should return another text.

I'm using =iif(instr(" is",[field_name])=0,"OK","not OK")

and it is not working it is always returning Not ok

Thanks for your help

 

[JHB]

Your order in InStr is not correct, try :

iif(instr([field_name], " is")=0,"OK","not OK")

 

[MSAccessRookie]

would like to make an expression in an Access report, that if the field contains a space and the word "is" it should return one text and if it doesn't contain it should return another text.

I'm using =iif(instr(" is",[field_name])=0,"OK","not OK")

and it is not working it is always returning Not ok

Thanks for your help

The syntax for the Instr function is:

Instr ( startingposition, string_being_searched, stringtocompare )

You have provided the string_being_searched and the stringtocompare, but you have left out the startingposition parameter. I suggest using the number 1.

-- Rookie

 

[JHB]

If startposition omitted, search begins at the first character position.

From the MS-Access help file:

InStr([start, ]string1, string2[, compare])
The InStr function syntax has these arguments:
Part Description
start Optional. Numeric expression that sets the starting position for each search. If omitted, search begins at the first character position.
If start contains Null, an error occurs.
The start argument is required if compare is specified.
string1 Required. String expression being searched.
string2 Required. String expression sought.
compare Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison.