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?
Filter SQL
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Filter SQL
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter SQL
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*"
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*"
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Filter SQL
Hans
Any way to exceed the 1024 characters in the query design or should I start another criteria line underneath ?
Any way to exceed the 1024 characters in the query design or should I start another criteria line underneath ?
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter SQL
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):
Or switch to SQL view, enter the complete condition there, and don't switch to Design view any more.
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):
Or switch to SQL view, enter the complete condition there, and don't switch to Design view any more.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England
Re: Filter SQL
So if I enter the OWN_NME column again, I can then enter (example) another 1024 characters as long as I untick the show ?
Cheers ...
Dave.
Dave.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter SQL
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1728
- Joined: 25 Jan 2010, 08:34
- Location: Stoke on Trent - Staffordshire - England