problem with 'in' operator, if used with select command

[Guest]

select query has problem with 'in' operator.
for ex. select * from modelmaster where model in ('a','b','c')
will sometimes return partial recordset, (most often with complex quries)
help...

 

[Tom Ellison]

Dear Sanjay:

Are there any rows in modelmaster where the model is exactly 'a' or
'b' or 'c'? Or is this perhaps just the first letter of the model?
If so, you could use:

SELECT * FROM modelmaster WHERE model LIKE "[abc]*"

or for MSDE

SELECT * FROM modelmaster WHERE model LIKE "[abc]%"

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thaks Sir,

I know well the in operator is used to get all the records for which you
supply the values with in operator (as IN ('a','b','c'). a,b,c are seperate
field values, not abc as a part of field)

I used IN operator in my vb project with 'jet driver' to generate reports,
if I got incomplete recordset at first attempt, at the second attempt, I got
all the records I expect and sometimes different records are missing than in
first attempt.

Sanjay Limbikai

Dear Sanjay:

Are there any rows in modelmaster where the model is exactly 'a' or
'b' or 'c'? Or is this perhaps just the first letter of the model?
If so, you could use:

SELECT * FROM modelmaster WHERE model LIKE "[abc]*"

or for MSDE

SELECT * FROM modelmaster WHERE model LIKE "[abc]%"

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

select query has problem with 'in' operator.
for ex. select * from modelmaster where model in ('a','b','c')
will sometimes return partial recordset, (most often with complex quries)
help...