A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 27th, 2009, 07:28 PM posted to microsoft.public.access.queries
Jey
external usenet poster
 
Posts: 27
Default help using a parameter in an 'In (...)' criteria

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



  #2  
Old January 27th, 2009, 08:49 PM posted to microsoft.public.access.queries
MGFoster
external usenet poster
 
Posts: 653
Default help using a parameter in an 'In (...)' criteria

-----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 wrote:
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


  #3  
Old January 27th, 2009, 11:37 PM posted to microsoft.public.access.queries
Jey
external usenet poster
 
Posts: 27
Default help using a parameter in an 'In (...)' criteria

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

  #4  
Old January 28th, 2009, 01:10 AM posted to microsoft.public.access.queries
MGFoster
external usenet poster
 
Posts: 653
Default help using a parameter in an 'In (...)' criteria

-----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+ijoechKqOuFEgEQLUrQCfbQAOI0JM3txA6MqrEcfqFM 9Hs9UAn065
FKkOUNqtNtb5F+/J/tmyBlFu
=bW+K
-----END PGP SIGNATURE-----

Jey wrote:
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

  #5  
Old January 28th, 2009, 09:07 PM posted to microsoft.public.access.queries
Jey
external usenet poster
 
Posts: 27
Default help using a parameter in an 'In (...)' criteria

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
  #6  
Old January 30th, 2009, 05:35 AM posted to microsoft.public.access.queries
MGFoster
external usenet poster
 
Posts: 653
Default help using a parameter in an 'In (...)' criteria

-----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 wrote:
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



  #7  
Old January 30th, 2009, 07:50 PM posted to microsoft.public.access.queries
Jey
external usenet poster
 
Posts: 27
Default help using a parameter in an 'In (...)' criteria

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
[Table]![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]));


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.