Parameter query using the IN clause

  • Thread starter Thread starter Archives
  • Start date Start date
A

Archives

Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks
 
Archives said:
Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks
Click to expand...
Hi Archives,

A typical solution is to use LIKE
instead of IN.

For instance, if you can train your
users to separate multi-parameters
with a semicolon:

Select * From MyTable
Where
(";" & [MyParameter] & ";")
LIKE ("*;" & MyTable_ID & ";*" );


Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Thank for your answer.

Your code works fine but unfortunately, it seems that the clause LIKE don't
use the primary index of the table (MyTable_ID is well a primary index). So,
for huge tables, the answer time is too hight.

It is why I was thinking about a IN clause. this clause use indexes.


Gary Walter said:
Archives said:
Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks
Click to expand...
Hi Archives,

A typical solution is to use LIKE
instead of IN.

For instance, if you can train your
users to separate multi-parameters
with a semicolon:

Select * From MyTable
Where
(";" & [MyParameter] & ";")
LIKE ("*;" & MyTable_ID & ";*" );


Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
je m'excuse, mais...

I just don't know of a way
to make a multi-parameter work
with an IN clause.

IN expects form

Select * From MyTable Where
MyTable_ID IN ('12','25')

and I have never figured out
how to convert so it works.

With a large dataset, the 2 efficient
ways I would tackle this are:

1)VBA...ask for parameters through inputbox
then parse user input, and rewrite SQL

2) use form with 2 listboxes bound to 2
tables (tblAvailable and tblSelected)

when open form fill tblAvailable with
all MyTable_ID's and clear tblSelected.

Put command buttons between listboxes
and when some are selected, pushing on
command button moves ID's from one table
to the other.

Your query would then be joined to tblSelected.

An example of this can be found on Helen's site

Access Archon Column #48
http://ulster.net/~hfeddema/access.htm


Archives said:
Thank for your answer.

Your code works fine but unfortunately, it seems that the clause LIKE don't
use the primary index of the table (MyTable_ID is well a primary index). So,
for huge tables, the answer time is too hight.

It is why I was thinking about a IN clause. this clause use indexes.


Gary Walter said:
Archives said:
Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks
Click to expand...
Hi Archives,

A typical solution is to use LIKE
instead of IN.

For instance, if you can train your
users to separate multi-parameters
with a semicolon:

Select * From MyTable
Where
(";" & [MyParameter] & ";")
LIKE ("*;" & MyTable_ID & ";*" );


Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
The zip file you would want from
Helen's site would be:

72. Paired Multi-Select Listboxes accarch72.zip


Gary Walter said:
je m'excuse, mais...

I just don't know of a way
to make a multi-parameter work
with an IN clause.

IN expects form

Select * From MyTable Where
MyTable_ID IN ('12','25')

and I have never figured out
how to convert so it works.

With a large dataset, the 2 efficient
ways I would tackle this are:

1)VBA...ask for parameters through inputbox
then parse user input, and rewrite SQL

2) use form with 2 listboxes bound to 2
tables (tblAvailable and tblSelected)

when open form fill tblAvailable with
all MyTable_ID's and clear tblSelected.

Put command buttons between listboxes
and when some are selected, pushing on
command button moves ID's from one table
to the other.

Your query would then be joined to tblSelected.

An example of this can be found on Helen's site

Access Archon Column #48
http://ulster.net/~hfeddema/access.htm


Archives said:
Thank for your answer.

Your code works fine but unfortunately, it seems that the clause LIKE don't
use the primary index of the table (MyTable_ID is well a primary index). So,
for huge tables, the answer time is too hight.

It is why I was thinking about a IN clause. this clause use indexes.


Gary Walter said:
Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks

Hi Archives,

A typical solution is to use LIKE
instead of IN.

For instance, if you can train your
users to separate multi-parameters
with a semicolon:

Select * From MyTable
Where
(";" & [MyParameter] & ";")
LIKE ("*;" & MyTable_ID & ";*" );


Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Thanks a lot.

I think I'ill use the 1) way, using a VBA code.

I expected a more direct way, but I can rebuilt a Select with a VBA routine.


Gary Walter said:
je m'excuse, mais...

I just don't know of a way
to make a multi-parameter work
with an IN clause.

IN expects form

Select * From MyTable Where
MyTable_ID IN ('12','25')

and I have never figured out
how to convert so it works.

With a large dataset, the 2 efficient
ways I would tackle this are:

1)VBA...ask for parameters through inputbox
then parse user input, and rewrite SQL

2) use form with 2 listboxes bound to 2
tables (tblAvailable and tblSelected)

when open form fill tblAvailable with
all MyTable_ID's and clear tblSelected.

Put command buttons between listboxes
and when some are selected, pushing on
command button moves ID's from one table
to the other.

Your query would then be joined to tblSelected.

An example of this can be found on Helen's site

Access Archon Column #48
http://ulster.net/~hfeddema/access.htm


Archives said:
Thank for your answer.

Your code works fine but unfortunately, it seems that the clause LIKE don't
use the primary index of the table (MyTable_ID is well a primary index). So,
for huge tables, the answer time is too hight.

It is why I was thinking about a IN clause. this clause use indexes.


Gary Walter said:
Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks

Hi Archives,

A typical solution is to use LIKE
instead of IN.

For instance, if you can train your
users to separate multi-parameters
with a semicolon:

Select * From MyTable
Where
(";" & [MyParameter] & ";")
LIKE ("*;" & MyTable_ID & ";*" );


Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Hi,


If you have a large table, use an ad hoc table for the in-list and an
inner join:

FROM myTable INNER JOIN inList ON myTable.FieldName = inList.List


instead of

FROM myTable
WHERE FieldName IN (list )


Have one record per value in the list, in table inList (no duplicated values
allow), and the inner join will act, in that circumstance, like a
intersection of the two sets (myTable and inLIst)...



Hoping it may help,
Vanderghast, Access MVP



Archives said:
Thank for your answer.

Your code works fine but unfortunately, it seems that the clause LIKE don't
use the primary index of the table (MyTable_ID is well a primary index). So,
for huge tables, the answer time is too hight.

It is why I was thinking about a IN clause. this clause use indexes.


Gary Walter said:
Archives said:
Hello,

How can I use a query using a parameter with the IN clause ? For example:

Select * From MyTable Where MyTable_ID IN ([MyParameter])

If MyParameter = "12", it works fine, but if I enter ""12;25", an error
occurs.

How can I solve this problem ? Thanks
Click to expand...
Hi Archives,

A typical solution is to use LIKE
instead of IN.

For instance, if you can train your
users to separate multi-parameters
with a semicolon:

Select * From MyTable
Where
(";" & [MyParameter] & ";")
LIKE ("*;" & MyTable_ID & ";*" );


Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Back
Top