Parameter query using the IN clause

[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

 

[Gary Walter]

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

 

[Archives]

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.

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

 

[Gary Walter]

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

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.

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

 

[Gary Walter]

The zip file you would want from
Helen's site would be:

72. Paired Multi-Select Listboxes accarch72.zip

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

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.

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

 

[Archives]

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.

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

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.

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

 

[Michel Walsh]

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

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.

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