Like Function **

[hycho]

Hi All,

I am trying to create the below iif statement:

Iif([Termed_Ind] is like *true*, 1, 0)

However, when I try running the query I get error message below:

"The expression you enters contain invalid syntax."

The field "termed_ind" has some of the following values below:

1. "falsefalsefalse"
2. "falsetruefalse"
3. "falsefalseTrue"
4. etc.

Does anyone know a way I can grouping these "true" values simply?

Thanks.

 

[plog]

You need to use the Like keyword with a string, that means putting quote marks around what you are testing for:

Iif([Termed_Ind] is like "*true*", 1, 0)

 

[Brianwarnock]

Don't think you use is with Like

 

[Pat Hartman]

You are mixing VBA and SQL syntax. The IIf() is a VBA function and the "LIKE" is SQL Syntax.

If you want a 1 or 0 to be returned from an expression, you will need to use InStr() inside the IIf()

Iif(Instr([Termed_Ind], "true") > 0, 1, 0)


If you want to select rows where Termed_Ind contains true then you need a WHERE clause:

Select ...
From ...
WHERE Termed_Ind Like "*true*";

 

[Brianwarnock]

Whilst I would certainly have used instr in VBA it is perfectly possible to use

Iif([Termed_Ind] Like "*true*", 1, 0)

in the field row of a query, I do not know if there is a reason for not doing so.

The poster apparently wants to group on the result, not too sure what he actually means by that.

Brian