Can You Use "In (Function())" in the Criteria Row of a Query? Options

[PatrickA]

ALl,

Thanks to information I've found on these groups, I was able to build
a function and use it in the Criteria row of a query.

This works fine now for things where my Criteria only has one value -
as in a single person.

Is there any magic to getting this to work when I want my Criteria to
be an IN comparison, e.g. IN (19,28,29)?

Right now, I have my function fGetStrQRYWhereP() returning:

IN (19,28,29)

But putting that in the Criteria row of the query returns 0 records.

Similary, trying to build the Criteria from bits and pieces (changing
my code so fGetStrQRYWhereP() returns 19,28,29 and then putting "In ("
& fGetStrQRYWhereP() & ")" in the Criteria row) returns 0 records.
The composite string looks correct when I show it in a query column,
though...

Any suggestions? Another approach? Am I trying to do something that
is not possible?

Thanks,

Patrick

 

[Dirk Goldgar]

ALl,

Thanks to information I've found on these groups, I was able to build
a function and use it in the Criteria row of a query.

This works fine now for things where my Criteria only has one value -
as in a single person.

Is there any magic to getting this to work when I want my Criteria to
be an IN comparison, e.g. IN (19,28,29)?

Right now, I have my function fGetStrQRYWhereP() returning:

IN (19,28,29)

But putting that in the Criteria row of the query returns 0 records.

Similary, trying to build the Criteria from bits and pieces (changing
my code so fGetStrQRYWhereP() returns 19,28,29 and then putting "In ("
& fGetStrQRYWhereP() & ")" in the Criteria row) returns 0 records.
The composite string looks correct when I show it in a query column,
though...

Any suggestions? Another approach? Am I trying to do something that
is not possible?

Unfortunately, you can't do it that way. What happens then is that your
entire criterion is treated as the string that has to match the field, so
you get an effective criterion that is like this:

YourField = "IN (19,28,29)"

Naturally, that's not going to match.

There are two main ways to work around this. The most efficient, where
feasible, is to build the whole SQL statement on the fly. That doesn't work
so well with stored queries, but it can be used when assigning recordsources
and rowsources dynamically, or when opening a recordset.

The second way is to have your function return a delimited string like
(maybe) this:

,19,28,29,

(note the leading and trailing delimiters), and use a query criterion like
this:

InStr("," & YourField & ",", fGetStrQRYWhereP()) > 0

Criteria like that are not very efficient, since the VBA function InStr has
to be called for every record, and no indexes can be used, but they can work
for relatively small tables or when performance is not an issue.

 

[Ken Sheridan]

The IN operator will only accept a literal value list as its argument,
but you can supplement it using one of the methods described at:

http://support.microsoft.com/kb/100131/en-us

Using the InParam function for instance, you'd firstly have to amend
you function so it returns:

19,28,29

i.e. without the IN or the parentheses, and call it in the query like
so:

SELECT *
FROM [YourTable]
WHERE InParam([YourField],fGetStrQRYWhereP());

Ken Sheridan
Stafford, England

 

[Dale Fye]

I have not found any convenient way to do this in a query, without actually
building the SQL string in VBA.

However, depending on what you are using this query for, you could get the
same functionality by using your function in the Where parameter of the
OpenForm or OpenReport method, or setting the Filter property of a form.

Something like:

docmd.OpenForm "formName",,,"[fieldName]" & fnGetStrQryWhereP()

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[a a r o n . k e m p f]

I do stuff like this _ALL_ the time in SQL Server


I found a function from www.novicksoftware.com and renamed it to
fnSplit
then I can write SQL Like this, and it's quite easy-- and this does
_EXACTLY_ what you're trying to do

Select *
From EMployees
Where ManagerID IN (Select Item from dbo.fnSplit
('123,124,125,556,985,43,3232,2344,342',',')


I honestly think that this does exactly what you're trying to do.
In other words-- the answer is 'Move to SQL Server because Jet is
obsolete, and it has been for a decade'.

-Aaron

 

[a a r o n . k e m p f]

and for the record, this also allows you to use _ANY_ operator you
want.. say that you're dealing with a list of numbers, seperated by
periods-- for example, something that would look like an IP address.

then you could just change the 2nd argument in the split function

select * from employees where managerid in (select item from
dbo.fnSplit('123.124.45.12', '.') )

It's got to be the most flexible thing in the world-- I use it for tab
characters and reading tab-seperated values sometimes.

-Aaron

 

[Aaron Kempf knows nothing]

and for the record, you're dealing with an idiot if you listen to me.

 

[Aaron Kempf knows nothing]

I do stupid stuff ALL_ the time