Parameter query-multiple filters,wildcard entries?

[Mike Turner]

Using a parameter query,is there a way-preferably using
only one parameter dialog box-of entering more than one
parameter value and "wildcard" values?

eg take a text field called Result,say

Would like to display:

eg: all records where the Result field has value L or R
(i.e. L OR R)

eg: all records where Result field starts with M (so need
a sort of M* approach)

Also,is there a way of storing filters which user might
use a lot?

eg:an "L OR R" filter
eg:an "M*" filter
eg:an "L OR (NOT C)

It would be handy to store such "standard" filters and
for the user to select them as needed.

Thanks very much for any help/comments.

Mike Turner

 

[Michel Walsh]

Hi,



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



with parameter entered as, for illustration, 1,3,6,7,8 would return records where their
FieldName value is either 1, 3, 6, 7 or 8. That is for Jet+DAO. Use the wildcard % rather than the
wildcard * if you use ADO.

It assumes the tables are relatively small. For large table, put the value in a small table, one
field, one value per record, and make an INNER JOIN. Operator LIKE use as here up cannot use index,
so, for tables with more than 1000 records, the table scan may become painful, in time of
execution.



Hoping it may help,
Vanderghast, Access MVP

 

[Mike Turner]

Hi Michel,
Thanks very much for your answer-it is just what I was
looking for.

I hope you might be able to help with a further
question.Given your example of entering 1,3,6,7,8 is
there also a way of coding "all values BUT 1,3,6,7,8"?

If you don't think there is a way,even that would help!

Thanks very much

Mike

-----Original Message-----
Hi,



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



with parameter entered as, for illustration,

1,3,6,7,8 would return records where their

FieldName value is either 1, 3, 6, 7 or 8. That is for

Jet+DAO. Use the wildcard % rather than the

wildcard * if you use ADO.

It assumes the tables are relatively small. For large

table, put the value in a small table, one

field, one value per record, and make an INNER JOIN.

Operator LIKE use as here up cannot use index,

so, for tables with more than 1000 records, the table

scan may become painful, in time of

 

[Michel Walsh]

Hi,


WHERE NOT ( previous_expression )


should do. It would return the records not returned with the previous expression.


Hoping it may help,
Vanderghast, Access MVP

Hi Michel,
Thanks very much for your answer-it is just what I was
looking for.

I hope you might be able to help with a further
question.Given your example of entering 1,3,6,7,8 is
there also a way of coding "all values BUT 1,3,6,7,8"?

If you don't think there is a way,even that would help!

Thanks very much

Mike

-----Original Message-----
Hi,



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



with parameter entered as, for illustration,

1,3,6,7,8 would return records where their

FieldName value is either 1, 3, 6, 7 or 8. That is for

Jet+DAO. Use the wildcard % rather than the

wildcard * if you use ADO.

It assumes the tables are relatively small. For large

table, put the value in a small table, one

field, one value per record, and make an INNER JOIN.

Operator LIKE use as here up cannot use index,

so, for tables with more than 1000 records, the table

scan may become painful, in time of

execution.



Hoping it may help,
Vanderghast, Access MVP






.

 

[Mike Turner]

Thanks very much Michel.Works fine

Mike

-----Original Message-----
Hi,


WHERE NOT ( previous_expression )


should do. It would return the records not returned with the previous expression.


Hoping it may help,
Vanderghast, Access MVP

Hi Michel,
Thanks very much for your answer-it is just what I was
looking for.

I hope you might be able to help with a further
question.Given your example of entering 1,3,6,7,8 is
there also a way of coding "all values BUT 1,3,6,7,8"?

If you don't think there is a way,even that would help!

Thanks very much

Mike

-----Original Message-----
Hi,



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

&
FieldName & "[, ]*" )

with parameter entered as, for illustration,

1,3,6,7,8 would return records where their

FieldName value is either 1, 3, 6, 7 or 8. That is

for
Jet+DAO. Use the wildcard % rather than the

wildcard * if you use ADO.

It assumes the tables are relatively small. For large

table, put the value in a small table, one

field, one value per record, and make an INNER JOIN.

Operator LIKE use as here up cannot use index,

so, for tables with more than 1000 records, the table

scan may become painful, in time of

execution.



Hoping it may help,
Vanderghast, Access MVP



Using a parameter query,is there a way-preferably using
only one parameter dialog box-of entering more than one
parameter value and "wildcard" values?

eg take a text field called Result,say

Would like to display:

eg: all records where the Result field has value L or R
(i.e. L OR R)

eg: all records where Result field starts with M (so need
a sort of M* approach)

Also,is there a way of storing filters which user might
use a lot?

eg:an "L OR R" filter
eg:an "M*" filter
eg:an "L OR (NOT C)

It would be handy to store such "standard" filters and
for the user to select them as needed.

Thanks very much for any help/comments.

Mike Turner






.

.