How do I use the Access OR and LIKE operators in an expression? L.

[Guest]

How do you use the Access OR and LIKE operators in an expression. I want to
say " IIf field value equals 1 or 2 or 3 or 4 then..." If you were designing
a query in Access then you would put "In (1,2,3)" on the criteria line to get
this result. Why won't the same comma separator work in an expression?

 

[Graham R Seach]

Richard,

You can do this a few ways:
IIf([SomeField] < 5, ValueIfTrue, ValueIfFalse)

....or...

IIf([SomeField] = 1, ValueIfTrue, IIf([SomeField] = 2, ValueIfTrue,
IIf([SomeField] = 3, ValueIfTrue, IIf([SomeField] = 4, ValueIfTrue,
ValeIfFalse))))

....or...

Choose([SomeField], ValueIfOne, ValueIfTwo, ValueIfThree, ValueIfFour)

....or...

Switch([SomeField] = 1, ValueIfOne, [SomeField] = 2, ValueIfTwo,
[SomeFIeld] = 3, ValueIfThree, [SomeField] = 4, ValueIfFour)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Brendan Reynolds]

IN() works for me. For example, in the Control Source of a form text box ...

=IIf([SourceID] In (1,2,3),"123","not")

 

[Guest]

Brendan,

thanks for that, I should have guessed that IN() would do it,

Richard Bancroft

IN() works for me. For example, in the Control Source of a form text box ...

=IIf([SourceID] In (1,2,3),"123","not")
--
Brendan Reynolds (MVP)

How do you use the Access OR and LIKE operators in an expression. I want
to
say " IIf field value equals 1 or 2 or 3 or 4 then..." If you were
designing
a query in Access then you would put "In (1,2,3)" on the criteria line to
get
this result. Why won't the same comma separator work in an expression?

 

[Guest]

Graham, many thanks for the suggestions. I've also had a suggestion to use
IN()) which works fine too,

Richard

Richard,

You can do this a few ways:
IIf([SomeField] < 5, ValueIfTrue, ValueIfFalse)

....or...

IIf([SomeField] = 1, ValueIfTrue, IIf([SomeField] = 2, ValueIfTrue,
IIf([SomeField] = 3, ValueIfTrue, IIf([SomeField] = 4, ValueIfTrue,
ValeIfFalse))))

....or...

Choose([SomeField], ValueIfOne, ValueIfTwo, ValueIfThree, ValueIfFour)

....or...

Switch([SomeField] = 1, ValueIfOne, [SomeField] = 2, ValueIfTwo,
[SomeFIeld] = 3, ValueIfThree, [SomeField] = 4, ValueIfFour)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

How do you use the Access OR and LIKE operators in an expression. I want
to
say " IIf field value equals 1 or 2 or 3 or 4 then..." If you were
designing
a query in Access then you would put "In (1,2,3)" on the criteria line to
get
this result. Why won't the same comma separator work in an expression?

 

[onedaywhen]

I should have guessed that IN() would do it,

FWIW you can you the LIKE keyword e.g.

SELECT MyIntCol LIKE '[1-3]' FROM MyTable;

Jamie.

--