help using a parameter in an 'In (...)' criteria

[Jey]

I have a series of queries that all have the same criteria: "In (2,3)" (plus
a few others). They are being called from VBA code, where the parameters are
generated and passed to the queries. I want the "In" criteria to be passed as
a parameter, since the list may not be '2,3' in every case. I can't seem to
make it work!

I've tried putting '[parameter]' as the criteria and passing 'In (2,3)' to
it, but I get an error message saying the expression is too complicated.

I've tried putting 'In ([parameter])' as the criteria and passing '2,3' to
it, but it doesn't return any records.

Any suggestions? Thanks in advance,
Jey

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use the InStr() trick:

1. Set up a Public function in the criteria form that passes a comma
delimited string of the parameters. If there aren't any parameters the
function should return zero (see below). If the function is passing
comma-delimited strings be sure to use single quotes - like this:

FunctionName = "'abcd', 'efghi', 'xyz'"


2. Set up the query to read that Public function:

PARAMETERS Forms!FormName!FunctionName Text(255);
....

3. Set up the WHERE clause with something like this:

IIf(Forms!FormName.FunctionName="0",True,InStr("," &
Forms!FormName.FunctionName & ",","," & column_name & ",")>0)

Substitute the correct column name that holds the data for which you
want to search for "column_name."

An english translation of the above: If the function returns 0 then use
"True" as a default for this expression. Otherwise, use the InStr()
trick to determine if any of the comma-delimited parameters are in the
"column_name" column.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSX9lOIechKqOuFEgEQLu/gCgpV4yAEdxNI6gOMuwPPjXsl/6iGoAoIPm
LXfTZlg2g7qOfG3ZAvYt1bix
=nc4N
-----END PGP SIGNATURE-----

 

[Jey]

Hi,
Sorry, but I'm not really clear on all that!
Ideally, I'd like to make a variable, define it as "2,3", then be able to
use the variable to define the paramater every time I call the query. Such as:

Dim DB As Database
Set DB = CurrentDb()
Dim rs As Recordset
Dim qdf As QueryDef
Dim strList As String
strList = "2,3"

Set qdf = DB.QueryDefs("qryMyQuery")
qdf.Parameters("parameter1") = .....
qdf.Parameters("parameter2") = .....
qdf.Parameters("parameter3") = strList

Set rs = qdf.OpenRecordset(dbOpenDynaset)
.....

It works for all the other parameters!!! The query is currently something
like:

SELECT ....
FROM .....
WHERE (((Field1.Table1)=[parameter1]) AND ((Field2.Table1)=[parameter2])
AND((Field3.Table1) In (2,3)));


What is it about the In () clause that won't accept "In ([parameter3])"???

Thanks,
Jey

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When you set a parameter like:

qdf.Parameters("parameter3") = strList

The parameter looks like this: "1,2,3"

That's ONE parameter instead of the required 3 parameters. That's why
it won't work in the IN () operator 'cuz instead of reading (1,2,3) it
is reading ("1,2,3"). It "thinks" it's looking for the string "1,2,3"
instead of the three numbers 1 2 3. That's why the InStr() trick is the
way to go.

You can assign the string to the parameter using:

qdf.Parameters("parameter3") = strList

Then, in the query, instead of using IN () use

InStr("," & parameter3 & "," , "," & column_name & ",") > 0

If one of the numbers in the parameter "parameter3" is stored in the
column the InStr() function returns a number > 0 - the expression >0
evaluates to True, which selects the record (depending on the other
criteria).

Substitute your column's name for "column_name."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSX+ijoechKqOuFEgEQLUrQCfbQAOI0JM3txA6MqrEcfqFM9Hs9UAn065
FKkOUNqtNtb5F+/J/tmyBlFu
=bW+K
-----END PGP SIGNATURE-----

 

[Jey]

Hi,

I can't get it to work!?! In the code I have:
strSurveyList = "2,3"
qdf.Parameters("parSurveyList") = strSurveyList

But I get an error when the code hits the qdf.Parameters... line, because
the query isn't asking for [parSurveyList].

In the query when I put InStr("," & "parSurveyList" & ",","," & "SurveyType"
& ",") > 0 as a criteria for the field SurveyType, table Survey... after
saving & closing the query when I re-open it the field is now InStr("," &
"parSurveyList" & ",","," & "SurveyType" & ",") and the criteria is > 0.

Shouldn't I have [parSurveyList] (in square brackets) somewhere so it knows
to ask for that parameter??
When I change the field to InStr("," & [parSurveyList] & ",","," &
"SurveyType" & ",")
it runs, but doesn't return the counts I'd expect if it was working right,
just nulls.

Jey

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Switch to SQL view.

At the top of the screen, before SELECT, put the PARAMETERS clause:

PARAMETERS parSurveyList Text, <the other parameter names and data
types. Don't enter the angle brackets. End the clause with the semi
colon.> ;
SELECT ...
FROM ...

NB: qdf.Parameters(0) is the first parameter in the PARAMETERS list, so
always use the parameter name when assigning values to parameters - this
will avoid errors.

You're correct about the square brackets, but you're putting them in the
wrong place. Should be like this:

WHERE InStr("," & parSurveyList & ",", "," & [SurveyType] & ",")>0

<other criteria>

Don't put quotes around the parameter name, nor the column name
(SurveyType).

When you work in the Design Grid, Access "helpfully" places quotation
marks around just about everything! That's why I work a lot in the SQL
View.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSYKDf4echKqOuFEgEQIEnACdFd5KD+6a21VP/yqqnFHH/Q6xfYIAoMIL
pR2Sa3W2EyB0pGjVH0eFARXq
=V4tz
-----END PGP SIGNATURE-----

 

[Jey]

Hi,

OK, I got it to work. Thanks!

I don't understand why my three other parameters are in square brackets in
the WHERE clause, but for the survey list parameter it's the field name that
is in square brackets? Hmm, now that I look at the SQL more closely I also
don't understand why in the SELECT clause the table/field are as

![Field] but in the WHERE they are as (Table.Field). Are those two
formats interchangeable? maybe that's where my confusion is coming from?

Here is my SQL:

PARAMETERS parSurveyList Text ( 255 ), parHerd Long, parPreDate DateTime,
parPostDate DateTime;
SELECT Sum([GroupLocation]![AdultUnknown]+[GroupLocation]![Unknown]) AS
[Unknown]
FROM Survey INNER JOIN GroupLocation ON Survey.Survey_ID =
GroupLocation.Survey_ID
WHERE (((GroupLocation.Herd_ID)=[parHerd]) AND ((InStr("," & parSurveyList &
",","," & [SurveyType] & ","))>0) AND ((GroupLocation.Date)>[parPreDate] And
(GroupLocation.Date)<[parPostDate]));