IN with LIKE

[rutica]

Can I use the IN keyword with LIKE?

I know I can do: iif([Lifecycle] in ("MS1-BeginPhase", "MS2-DesignPhase")

and I know I can do: iif([Lifecycle] like "MS1*" or [Lifecycle] like "MS2*"

But can I do something like: iif([Lifecycle] in (like "MS1*", like "MS2*")

Thanks,

 

[ajetrumpet]

This is extremely vague Rutica, so I guess I will say YES. Logically, your theory would work (whatever you mean by your examples), given the abilities of the query builder.

 

[Crey23]

Simply the answer is YES, but your statement is incorrect. the IN function should be used in subqueries.

I think you should be looking at something like:

iif([Lifecycle] in (like "MS1*", like "MS2*") (Your stament)

Iif([Lifecycle] like "MS1*" OR like "MS2*", ....)

I hope this helps.

 

[KenHigg]

To futher clarify, I think 'In' is a sql statement as opposed to a vba or access function...

 

[rutica]

Thanks to everyone who wrote.

ajetrumpet: I'm not sure how I was vague. I wrote exactly the IIF statement I was looking for.

Crey23: Your way doesn't work. You can't do:
Iif([Lifecycle] like "MS1*" OR like "MS2*", ....). I would have to write:
Iif([Lifecycle] like "MS1*" OR [Lifecycle] like "MS2*"... and I was trying to avoid repeating the field name. That is why I wanted to use IN.

KenHigg: I am trying to write this IIF statement using SQL in a query (not using VBA in a module).

 

[KenHigg]

Which is why it's important to know that IIF() is vba and In is sql. If I'm not mistaken the vba is compiled first then the sql. If the result of the In is not resolved when the vba is compiled an error is thrown.

I hope I have this right and it makes sense. In short you can't put the 'In' in the IIF().

 

[KenHigg]

Which is why it's important to know that IIF() is vba and In is sql. If I'm not mistaken the vba is compiled first then the sql. If the result of the In is not resolved when the vba is compiled an error is thrown.

I hope I have this right and it makes sense. In short you can't put the 'In' in the IIF().

Edit: But you can send it all out to a function

 

[rutica]

I have lots of IIF queries that use IN and they work fine.

I know I can do: iif([Lifecycle] in ("MS1-BeginPhase", "MS2-DesignPhase")

I was trying to use LIKE with IN to simplify.
I was trying to make this work somehow: iif([Lifecycle] in (like "MS1*", like "MS2*")

 

[KenHigg]

I have lots of IIF queries that use IN and they work fine.

I know I can do: iif([Lifecycle] in ("MS1-BeginPhase", "MS2-DesignPhase")

I was trying to use LIKE with IN to simplify.
I was trying to make this work somehow: iif([Lifecycle] in (like "MS1*", like "MS2*")

My bad. I had a problem with In a while back and thought I had my head around it. Apparently not -

I still think it has something to do with In() being sql statement (?) that's causing the problem... Who knows, I may have that wrong as well...

 

[Brianwarnock]

Have you tried Like "[ms1-ms2]*"

Brian