Quick Not In ( ) question

[robbydogg]

Hi,
In Access 2002, does the
Not In feature work? (or how does it as i may have made an error)

I'm using

Not in ("YQ", "YW", "YZ", "ZY", "ZX", "ZA")

Or could i use

Not in ("Y*", "Z*")



Thanks
Rob

 

[DCrake]

The In() works similar to OR but is easier to code, however it works on complete strings not partial and does not allow wildcards, sorry.

David

 

[Banana]

One possible workaround is to do something like:

LEFT(aString, 1) IN ("Y', "Z")

but I would probably prefer LIKE "Y*" OR LIKE "Z*" anyway because I want to be sure Jet will use indexes and not do a table scan.

 

[DCrake]

Expanding form the previous post create a new column in your query

FirstLetter:Left([fieldName],1)

Then use In("Y","Z")

 

[georgedwilkinson]

Also, realize that "NOT IN" is notoriously slow. If you have a large data set, you might want to optimize the query.

Since you didn't mention what problem you are having, there's not really much way to give you any further advice.

 

[Brianwarnock]

One possible workaround is to do something like:

LEFT(aString, 1) IN ("Y', "Z")

but I would probably prefer LIKE "Y*" OR LIKE "Z*" anyway because I want to be sure Jet will use indexes and not do a table scan.

Since he is using the Not wont that be an AND not an OR

Not LIKE "Y*" And Not LIKE "Z*"

Brian

 

[robbydogg]

Thanks for all your help.


I had changed the query to

Not like "Y*" and not like "Z*"

and it worked,
i just had to prove to an elder that it wasn't the same as doing a SQL query.

 

[Banana]

Since he is using the Not wont that be an AND not an OR

Not LIKE "Y*" And Not LIKE "Z*"

Brian

Ah, good catch. Thanks, Brian.

 

[Brianwarnock]

HTH a 5 jello man.

Brian

Just pleased that the old brain still works now and again.

 

[MSAccessRookie]

Ah, good catch. Thanks, Brian.

I would like to ask a question about a point that has not been discussed yet. In Post #1, the OP provided a list of choices ("YQ", "YW", "YZ", "ZY", "ZX", "ZA") that were to be the proper choices.

I believe that the proposed solution (Not LIKE "Y*" And Not LIKE "Z*") provides more than the OP needs, since it will also return true for values like "YF", "YS", "ZF", and "ZS". Does this mean that the suggested solutiuon is incomplete?

 

[Brianwarnock]

In that post he also asked if he could use "Y*" and "Z*"

Brian

 

[Banana]

Yes, the OP did list those but he then asked if he could do Y* and Z* instead so I had assumed that he wanted to exclude "YF", "YS", "ZF", "ZS" as well (maybe his business rules only permits those six values so it's nonissue anyway?). But yes, if he didn't intend this, then this wouldn't be an appropriate solution.

 

[MSAccessRookie]

Yes, the OP did list those but he then asked if he could do Y* and Z* instead so I had assumed that he wanted to exclude "YF", "YS", "ZF", "ZS" as well (maybe his business rules only permits those six values so it's nonissue anyway?). But yes, if he didn't intend this, then this wouldn't be an appropriate solution.

Thanks for confirming my suspicions. Interesting that I had a different take on the question, though. I presumed that the first example was the requirement, and the second one was a proposed solution that was not quite right.

 

[georgedwilkinson]

The OP didn't tell us his requirement or even mention what his problem (if any) was. He asked if a function works or not (then going on to say he could use something else?). An odd question since it is obvious that it works or Microsoft would not have included it in the product.