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

  • Thread starter Thread starter Jey
  • Start date Start date
J

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
 
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
 
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
 
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]));
 
Back
Top