List of Matches in WHERE Clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex Maghen - 10 Jul 2006 11:55 GMT

Hi. Is there a shorthand for matching a list of items in the WHERE clause?
Here's what I mean:

select * from SomeTable
where
(
(SomeColumn='A')
or
(SomeColumn='Q')
or
(SomeColumn='Y')
or
(SomeColumn='M')
)

Is there some way to make a list of things to match "SomeColumn" instead of
having to do many "OR"s?

Alex

Reply to this Message

Ofer Cohen - 10 Jul 2006 13:50 GMT

You can use

select * from SomeTable
where SomeColumn In ('A','Q','Y','M')

Signature

Good Luck
BS"D

> Hi. Is there a shorthand for matching a list of items in the WHERE clause?
> Here's what I mean:
[quoted text clipped - 15 lines]
>
> Alex

Reply to this Message

Alex Maghen - 10 Jul 2006 14:57 GMT

Brilliant! I didn't know you could do that. Then in that case, let me ask you
a related question - Let's say I don't know how many of those items in the
WHERE clause I'm going to be handline and I want to pass them as parameters
to an Access Query from my application. Is there a way to provide sort of an
open-ended list parameter to an Access query? The way you can have a "params
char[] SomeList" in a C# function declaration? Know what I mean?

In any case, THANKS!

Alex Maghen
Tel Aviv

> You can use
>
[quoted text clipped - 20 lines]
> >
> > Alex

Reply to this Message

Ofer Cohen - 10 Jul 2006 15:52 GMT

There is a problem with this method with passing unlimited amount of
parameters, this SQL

Select * From TableName Where FieldName In ([Please select parameter])

When the parameter is 'A','C' it wont look for A and C, it will look for a
string 'A','B' and not seperate.

In that case i would use some code to insert a new SQL to a Query

Application.CurrentDb.QueryDefs("QueryName").SQL = "Select * From TableName
Where FieldName In (" & StrParameter & ")"
================================
I hope it's clear.

Ofer Cohen
Modiin

Signature

Good Luck
BS"D

> Brilliant! I didn't know you could do that. Then in that case, let me ask you
> a related question - Let's say I don't know how many of those items in the
[quoted text clipped - 32 lines]
> > >
> > > Alex

Reply to this Message

Michel Walsh - 10 Jul 2006 23:01 GMT

Hi,

You can also use:

WHERE ( "," & parameter & "," ) LIKE ( "*[, ]" & fieldName & "[, ]*" )

as long as the parameter list is something like: "1, 2, 44, 55"

but it can be slow. Another alternative is to make a small table, one field,
one value per field, no dup, and use an inner join between that table and
the original table to filter.

Hoping it may help,
Vanderghast, Access MVP

> Brilliant! I didn't know you could do that. Then in that case, let me ask
> you
[quoted text clipped - 38 lines]
>> >
>> > Alex

Reply to this Message