Filter SQL

[D Willett]

Hi

I'm pulling fleet data from a table using the following query:

SELECT tblEST.EST_NO, tblEST.SUP_NO, tblEST.REG, tblEST.STA, tblEST.U_DTE, tblEST.OWN_ADD_1, tblEST.OWN_ADD_2, tblEST.OWN_ADD_3, tblEST.OWN_ADD_4, tblEST.OWN_PCD, tblEST.OWN_TEL_H, tblEST.OWN_TEL_W, tblEST.Client, tblEST.VEH_MAK, tblEST.VEH_MOD, tblEST.VEH_TRM_NO, tblEST.ENQ_SRC, tblEST.F_DTE, tblEST.OWN_NME
FROM tblEST
WHERE (((tblEST.OWN_NME) Not In ("Mr*","Mrs*","Miss*","Ms*")));

The only way I can do this is by removing anything with Mr Mrs Miss etc.
I thought the above would work but the result still has entries with Mr Mrs etc.

Any idea's why it doesn't work?

[HansV]

Hi Dave,

You can't use wildcards with a condition of the form In (...) or Not In (...). You have to use Not Like and specify each string separately:

WHERE OWN_NME Not Like "Mr*" And OWN_NME Not Like "Mrs*" And OWN_NME Not Like "Miss*" And OWN_NME Not Like "Ms*"

[D Willett]

Got it under control now Hans.

Thanks for the help..

[D Willett]

Hans

Any way to exceed the 1024 characters in the query design or should I start another criteria line underneath ?

[HansV]

If your condition is longer than 1,024 characters, you must break it up. In this example, you should not use two criteria lines, for if you have

Condition 1
Condition 2

it is evaluated as Condition 1 Or Condition 2, but we want And. You can accomplish this by adding the OWN_NME column several times, clearing the Show check box for all except the first instance, and entering one condition under each. Here is an example (in another table):

x70.png

Or switch to SQL view, enter the complete condition there, and don't switch to Design view any more.

[D Willett]

So if I enter the OWN_NME column again, I can then enter (example) another 1024 characters as long as I untick the show ?

[HansV]

Yes, indeed. BTW, clearing the Show check box is mostly for aesthetic reasons: if you leave the Show check box ticked, the OWN_NME field would be displayed twice (or more) in the query result, which is not attractive.

[D Willett]

Thank you again.