using 'IN' in a vba if statement

[bobh]

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

[bobh]

On Aug 14, 11:02 am, bobh <[e-mail]:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

maybe I answered my own question with;

If MyRec!ClmTyp = "p" And MyRec!PolSym = (I Or K Or O Or P Or Q Or R
Or S Or Z) Then

[RoyVidar]

bobh wrote:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

You'll need the Like operator, something like this

.... and MyRec!PolSym Like "[IKOPQRSZ]" then

which should work, I think, if the PolSym field contains only
one letter.

--
Roy-Vidar

[bobh]

On Aug 14, 11:52 am, bobh <[e-mail]:

On Aug 14, 11:02 am, bobh <[e-mail]:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

maybe I answered my own question with;

If MyRec!ClmTyp = "p" And MyRec!PolSym = (I Or K Or O Or P Or Q Or R
Or S Or Z) Then

I spoke too soon cause this doesn't work either

If MyRec!ClmTyp = "p" And MyRec!PolSym = (I Or K Or O Or P Or Q Or R

Or S Or Z) Then <<

[John Winterbottom]

if PolSym is a text data type you need quote marks around the criteria:

.... in ('I','K','O','P','Q','R','S','Z')
bobh wrote:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

[bobh]

Hi,
Yes polsym is text but " in ('I','K','O','P','Q','R','S','Z') " does
not work in the vba if statement.
thanks
bobh.

On Aug 14, 1:25 pm, John Winterbottom <[e-mail]:

if PolSym is a text data type you need quote marks around the criteria:

... in ('I','K','O','P','Q','R','S','Z')

bobh wrote:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.- Hide quoted text -

- Show quoted text -

[John Winterbottom]

I think the problem is not with the IN operator. Try printing the
vb-generated sql string to the debug window and copy into a query and
run it from there

bobh wrote:

Hi,
Yes polsym is text but " in ('I','K','O','P','Q','R','S','Z') " does
not work in the vba if statement.
thanks
bobh.

On Aug 14, 1:25 pm, John Winterbottom <[e-mail]:

>if PolSym is a text data type you need quote marks around the criteria:

... in ('I','K','O','P','Q','R','S','Z')

bobh wrote:

>>Hi,
In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.
if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then
thanks
bobh.- Hide quoted text -

- Show quoted text -

[fredg]

On Tue, 14 Aug 2007 08:02:41 -0700, bobh wrote:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

If I recall correctly, In is not a VBA operator, so it's not
recognized in VBA.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[fredg]

On Tue, 14 Aug 2007 10:08:45 -0700, bobh wrote:

On Aug 14, 11:52 am, bobh <[e-mail]:

>On Aug 14, 11:02 am, bobh <[e-mail]:

>>Hi,

>>In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

>>if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

>>thanks
bobh.

maybe I answered my own question with;

If MyRec!ClmTyp = "p" And MyRec!PolSym = (I Or K Or O Or P Or Q Or R
Or S Or Z) Then

I spoke too soon cause this doesn't work either

>If MyRec!ClmTyp = "p" And MyRec!PolSym = (I Or K Or O Or P Or Q Or R

Or S Or Z) Then <<

No that won't work either.
You need to include the criteria field with each value.
Note the parenthesis placement as well.

If MyRec!ClmTyp = "p" And (MyRec!PolSym = "I" Or MyRec!PolSym = "K"
Or MyRec!PolSym = "O" Or MyRec!PolSym = "P" etc... ) then
should work.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[paii, Ron]

"bobh" <vu******@yahoo.comwrote in message
news:11*********************@r34g2000hsd.googlegro ups.com...

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

IF MyRec!ClmTyp="p" and instr(1, "I,K,O,P,Q,R,S,Z", MyRec!PolSym)>0 then

Note the commas in "I,K,O,P,Q,R,S,Z" are not needed and can cause error if
the value of [PolSym] can be comma.

[CDMAPoster]

On Aug 14, 2:33 pm, fredg <[e-mail]:

On Tue, 14 Aug 2007 08:02:41 -0700, bobh wrote:

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

If I recall correctly, In is not a VBA operator, so it's not
recognized in VBA.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

I believe fredg is correct.

How about:

If CStr(Nz(MyRec!ClmTyp,"")) = "p" Then
Select Case CStr(Nz(MyRec!PolSym, ""))
Case "I", "K", "O", "P", "Q", "R", "S", "Z":
'do stuff
End Select
End If

James A. Fortune
CD********@FortuneJames.com

[Salad]

bobh wrote:

Hi,
Yes polsym is text but " in ('I','K','O','P','Q','R','S','Z') " does
not work in the vba if statement.
thanks
bobh.

On Aug 14, 1:25 pm, John Winterbottom <[e-mail]:

>>if PolSym is a text data type you need quote marks around the criteria:

... in ('I','K','O','P','Q','R','S','Z')

bobh wrote:

>>>Hi,

>>>In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

>>>if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

>>>thanks
bobh.- Hide quoted text -

- Show quoted text -

Why not use Instr()
And Instr("ABCDEFG","A:) 0

[Bob Quintal]

bobh <vu******@yahoo.comwrote in
news:11*********************@r34g2000hsd.googlegro ups.com:

Hi,

In a query one can use the 'IN' operator but how can I do the
same thing in a if statement?? as in the below which does not
work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

the instr() function returns the position of a specified string
within another string, or a 0 if not found.

if instr("IKOPQRSZ",MyRec!PolSym) >0 then

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[bobh]

This worked..... :) Thanks!
bobh.

On Aug 14, 3:22 pm, "paii, Ron" <[e-mail]:

"bobh" <[e-mail] in message

news:11*********************@r34g2000hsd.googlegro ups.com...

Hi,

In a query one can use the 'IN' operator but how can I do the same
thing in a if statement?? as in the below which does not work.

if MyRec!ClmTyp="p" and MyRec!PolSym in (I,K,O,P,Q,R,S,Z) then

thanks
bobh.

IF MyRec!ClmTyp="p" and instr(1, "I,K,O,P,Q,R,S,Z", MyRec!PolSym)>0 then

Note the commas in "I,K,O,P,Q,R,S,Z" are not needed and can cause error if
the value of [PolSym] can be comma.