Function Inside an "IN" Operator

[John Lane]

Can there be a function inside an IN operator, such as:

IN (GetstrWhere())

where GetstrWhere returns a string bounded by quotes (")? The function
executes and returns data, but the IN opeator fails and does not make a match
to the data from the table. I think the embedded quotes are messing it up,
but I'm not sure.

 

[Stefan Hoffmann]

hi John,

Can there be a function inside an IN operator, such as:
IN (GetstrWhere())

No, this is not possible. But why don't you change your function, e.g.

WHERE [yourField] IN (GetstrWhere())

=>

WHERE GetstrWhere([yourField])

with

Public Function GetstrWhere(AValue As Variant) As Boolean

Dim Result As Boolean

If IsNull(AValue) Then
Result = False
Else
'your tests against AValue
End If

GetstrWhere = Result

End Function



mfG
--> stefan <--

 

[Bob Barrows]

Can there be a function inside an IN operator, such as:

IN (GetstrWhere())
No.

where GetstrWhere returns a string bounded by quotes (")? The function
executes and returns data, but the IN opeator fails and does not make
a match to the data from the table. I think the embedded quotes are
messing it up, but I'm not sure.

The IN clause requires either a comma-delimited list of values or a
SELECT statement that returns a single column of values.

Your function, from the sounds of it, is returning a string containing
some characters, that include one or more commas. This string _is a
single value_. The query engine _will not_ parse that string into a list
of values.

Here is my canned reply on parameterizing the IN () clause:

Here is a compilation of posts about how to deal with this issue:
There are two solutions for this problem listed in the following KB
article
(Q210530 - ACC2000: How to Create a Parameter In() Statement), found by
searching for the keywords "parameter list query" (no quotes) at
http://support.microsoft.com.

http://support.microsoft.com/suppor...s/Q210/5/30.ASP

The first solution uses Instr() to test the field values against the
list in
the parameter. The second involves dynamically creating a SQL statement
in
code.

Thanks to Paul Overway, here is a third solution, using the Eval
function:

WHERE (((Eval(

![Field] & " In(" &
[Forms]![Formname]![textboxname] &
")"))=True))

or, using a prompted parameter:

WHERE (((Eval(

![Field] & " In(" & [Enter List] & ")"))=True))


Thanks to Jeffrey A. Williams, here's a 4th solution:

If you don't mind adding a table to your database, and you're
comfortable
dealing with possible multi-user issues, this will perform better than
either of the solutions that involve running a function (Instr or Eval)
on
every row of your table:

Create a new table with two fields:

tblCriteria:
Criteria text
Selected boolean (yes/no)

Populate the table with your values and select a couple of items. Now
you
can use this table in your query as such:

Select * from table1
inner join tblcriteria
on table1.[your criteria field] = tblcriteria.criteria
where tblcriteria.selected = true

You can easily setup a form (or subform) that is bound to tblCriteria
and
allow the users the
ability of selecting which values they want.



Thanks to Michel Walsh, here's yet another way:

SELECT Table3.ConName, Table3.State, Table3.Zip
FROM Table3
WHERE "," &

• & "," LIKE "*," & [ConName] & ",*"
  
  with [param] some string like: '1,4,5,7'
  
  note that there is no space after the comas.
  
  
  It works simply. If AccountID is 45, clearly ',1,4,5,7,' LIKE
  '*,45,*' returns false.
  If AccountID is 4, on the other hand, ',1,4,5,7,' LIKE '*,4,*'
  returns true.
  
  So, you have, in effect, an IN( ) where the list is a parameter.

 

[Marshall Barton]

Can there be a function inside an IN operator, such as:

IN (GetstrWhere())

where GetstrWhere returns a string bounded by quotes (")? The function
executes and returns data, but the IN opeator fails and does not make a match
to the data from the table. I think the embedded quotes are messing it up,
but I'm not sure.

No. A function returns a value, not a list of values as IN
requires.

You could approach this problem using the Like operator.
E.g. If the function returns a string like:
$a$b$c$
then use:
WHERE GetstrWhere() Like "*$" & thefield & "$*"