By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

using 'IN' in a vba if statement

P: n/a
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.

Aug 14 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
On Aug 14, 11:02 am, bobh <vulca...@yahoo.comwrote:
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

Aug 14 '07 #2

P: n/a
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
Aug 14 '07 #3

P: n/a
On Aug 14, 11:52 am, bobh <vulca...@yahoo.comwrote:
On Aug 14, 11:02 am, bobh <vulca...@yahoo.comwrote:
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 <<


Aug 14 '07 #4

P: n/a
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.
Aug 14 '07 #5

P: n/a
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 <john...@rogers.cawrote:
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 -

Aug 14 '07 #6

P: n/a
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 <john...@rogers.cawrote:
>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 -

Aug 14 '07 #7

P: n/a
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
Aug 14 '07 #8

P: n/a
On Tue, 14 Aug 2007 10:08:45 -0700, bobh wrote:
On Aug 14, 11:52 am, bobh <vulca...@yahoo.comwrote:
>On Aug 14, 11:02 am, bobh <vulca...@yahoo.comwrote:
>>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
Aug 14 '07 #9

P: n/a

"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.
Aug 14 '07 #10

P: n/a
On Aug 14, 2:33 pm, fredg <fgutk...@example.invalidwrote:
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

Aug 14 '07 #11

P: n/a
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 <john...@rogers.cawrote:
>>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
Aug 14 '07 #12

P: n/a
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

Aug 15 '07 #13

P: n/a
This worked..... :) Thanks!
bobh.

On Aug 14, 3:22 pm, "paii, Ron" <n...@no.comwrote:
"bobh" <vulca...@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.

Aug 15 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.