List of Matches in WHERE Clause

[Guest]

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

 

[Guest]

You can use

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

 

[Guest]

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

 

[Guest]

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


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

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

 

[Michel Walsh]

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

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