List of Matches in WHERE Clause

A

Alex Maghen

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
 
A

Alex Maghen

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
 
O

Ofer Cohen

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


Alex Maghen said:
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


Ofer Cohen said:
You can use

select * from SomeTable
where SomeColumn In ('A','Q','Y','M')
Click to expand...
Click to expand...
 
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



Alex Maghen said:
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


Ofer Cohen said:
You can use

select * from SomeTable
where SomeColumn In ('A','Q','Y','M')
Click to expand...
Click to expand...
 
Top