If Loc in(20,21,22,23,24) then.... how to do this in VB having problem

[nstouffer]

Access VB won't compile (recognize) the following simple SQL operator: in()

How do you perform this needed operation in access vb.

Ex:
Dim Loc as Integer
Dim sLocList as string
Loc = 20
sLocList = "20,21,22,23,24"

If Loc in(sLocList) then
''do this
else
'''do that
end if


It just won't recognize it..thanks for any info.

nstouffer

 

[scriverb]

Try this:

Dim Loc as Integer
Dim sLocList as string
Loc = 20
sLocList = "20,21,22,23,24"

If Instr(1 ,sLocList ,CStr(Loc)) > 0 then
''do this
else
'''do that
end if

This looks for the character location of Loc in the sLocList. If it is greater than 0 then you have found your match. Now you can eliminate the CStr function by Locading Loc = "20" so that it is already a string.

Give this a try as I use it often and it works well.
Bob Scriver

 

[mwolf00]

SELECT CASE loc
case 20,21,22,23,24
'do this
case else
'do that
END SELECT Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[JeremyNYC]

If loc >19 and loc <25 then
'One Thing
else
'Another Thing
end if

The reason you're having trouble with the original statement is that that In() looks for numeric data, and you're supplying as string. (I _think_ that's the reason, anyway.)

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[nstouffer]

thanks for the options, that solves it for my if..then statements, BUT, here is where I was hoping to apply it:

As a filter on the open event of a form, such as...

Me.Filter = &quot;Status = 'Open' AND ApprovedStatus = 'Not Approved' AND LocID in (&quot; & cLocList & &quot&quot;
Me.FilterOn = True

It just won't work, and the line Me.FilterOn = True causes an error: The previous operation has been cancelled.
Without the line, the filter doesn't work or won't take.
The cLocList variable is based on the user's security table to filter the PO records to only allow access to the specified centers i.e. 20,21,22,23 or just 24,28...
I don't believe I can use the Instr function in a filter string, should the in() work? And though I'm passing these numbers in as a string i though it wouldn't matter because the filter is a string and the numbers end up like (20,21) instead of ('20','21').. Any suggestions, thoughts, more comments?? Thank you all for help!

nstouffer

 

[JeremyNYC]

Well, first off it's a bit of a pain that you post a question for how to do one thing and then tell us that really you want to do something else. Try posting the actual question. It works.

Next, why not just use one of the solutions posted to come up with a boolean and make the boolean part of your expression?

Next, you're really going to want to use a solution that checks against a recordset. How were you planning on getting your data into this code? Plan the whole thing out from the start, let us know what you're doing, and then you'll get meaningful answers and we won't waste our time.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.