discrepancy between SQL and Design view

[Tonk]

What follows is a clunky delete query that is designed to get rid of
records where any of the following conditions are met:

1) sex is null
2) age is null
3) age is not one of the following: 5, 15, 25,35, 45, or 55
4 sex is not 1 or 2

As near as I can tell, it is working. (I'm new at this, so don't laugh
at its inefficiency!) Here's my dilemma - I build the query in the
design grid and then did some tweaking in the SQL view. When I got the
query to the point at which you see it below and shifted back to the
design view, I couldn't find any reference to criteria 3. Is that
normal? I thought if there was something in the SQL view, that it
would also show up in the design view. Any help would be very much
appreciated.

Mike

DELETE working.[Station #], working.[Check County], working.Ager,
working.Date, working.[County Harvest], working.Sex, working.Age,
working.[Right Beam], working.[Left Beam], working.[Right Points],
working.[Left Points], working.RecordNumber, (working.Age)>=5 And
(working.Age)<=55 AS Expr1
FROM working
WHERE (((working.Sex) Is Null)) Or (((working.Age) Is Null)) Or
(((working.Sex) Not In (1,2))) Or ((((working.Age)>=5 And
(working.Age)<=55)=False));

 

[John Vinson]

1) sex is null
2) age is null
3) age is not one of the following: 5, 15, 25,35, 45, or 55
4 sex is not 1 or 2

If Age is NULL, then you can be ABSOLUTELY SURE that it is not equal
to 5, 15, 25... or any other number.

Your first and second criteria make the third and fourth irrelevant.

Am I misunderstanding?

John W. Vinson[MVP]

 

[Tonk]

Hi John - Thanks very much for taking time to reply. A criteria are
needed. Not only do I want to delete records that may have a
missing/null value for sex, but I also want to delete those records
where age is 135 or 2 or 250 and so on. The same applies to sex. The
only valid values are 1 and 2. Some records are missing values and
some have values that were incorrectly keyed.

Happy New Year!

Mik

 

[Tonk]

Hey Jerry - Thanks so much for getting back to me. If you would like,
I would be happy to try and reproduce it for you and send you a shot of
what the SQL looked like before I modified it.

Just let me know.

Mike

-That is strange. I wish that we could see the SQL statement before it got
changed. By chance do all the Age values end in 5? Access might have made a
bad assumption. Is there a lookup on that field at table level?

The following should work:

DELETE working.*
FROM working
WHERE working.Sex Is Null
Or working.Sex Not In (1, 2)
Or working.Age Is Null
Or working.Age Not In (5, 15, 25, 35, 45, 55) ;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

What follows is a clunky delete query that is designed to get rid of
records where any of the following conditions are met:

1) sex is null
2) age is null
3) age is not one of the following: 5, 15, 25,35, 45, or 55
4 sex is not 1 or 2

As near as I can tell, it is working. (I'm new at this, so don't laugh
at its inefficiency!) Here's my dilemma - I build the query in the
design grid and then did some tweaking in the SQL view. When I got the
query to the point at which you see it below and shifted back to the
design view, I couldn't find any reference to criteria 3. Is that
normal? I thought if there was something in the SQL view, that it
would also show up in the design view. Any help would be very much
appreciated.

Mike

DELETE working.[Station #], working.[Check County], working.Ager,
working.Date, working.[County Harvest], working.Sex, working.Age,
working.[Right Beam], working.[Left Beam], working.[Right Points],
working.[Left Points], working.RecordNumber, (working.Age)>=5 And
(working.Age)<=55 AS Expr1
FROM working
WHERE (((working.Sex) Is Null)) Or (((working.Age) Is Null)) Or
(((working.Sex) Not In (1,2))) Or ((((working.Age)>=5 And
(working.Age)<=55)=False));

 

[David F Cox]

is it possible that you mixed up your "AND"s and "OR"s and set up the query
so that not between part of the WHERE clause wiped out that condition and
the query optimizer jettisoned that part of the query as unnecessary?
In simpler words, either you or Access got confused over the logic.
this confused me for a while:

Or ((((working.Age)>=5 And (working.Age)<=55)=False));

What follows is a clunky delete query that is designed to get rid of
records where any of the following conditions are met:

1) sex is null
2) age is null
3) age is not one of the following: 5, 15, 25,35, 45, or 55
4 sex is not 1 or 2

As near as I can tell, it is working. (I'm new at this, so don't laugh
at its inefficiency!) Here's my dilemma - I build the query in the
design grid and then did some tweaking in the SQL view. When I got the
query to the point at which you see it below and shifted back to the
design view, I couldn't find any reference to criteria 3. Is that
normal? I thought if there was something in the SQL view, that it
would also show up in the design view. Any help would be very much
appreciated.

Mike

DELETE working.[Station #], working.[Check County], working.Ager,
working.Date, working.[County Harvest], working.Sex, working.Age,
working.[Right Beam], working.[Left Beam], working.[Right Points],
working.[Left Points], working.RecordNumber, (working.Age)>=5 And
(working.Age)<=55 AS Expr1
FROM working
WHERE (((working.Sex) Is Null)) Or (((working.Age) Is Null)) Or
(((working.Sex) Not In (1,2))) Or ((((working.Age)>=5 And
(working.Age)<=55)=False));

 

[John Nurick]

PMFJI,

Surely all that's needed is

DELETE FROM working
WHERE (sex NOT IN (1,2))
OR (age NOT IN (5,15,25,35,45,55));

What follows is a clunky delete query that is designed to get rid of
records where any of the following conditions are met:

1) sex is null
2) age is null
3) age is not one of the following: 5, 15, 25,35, 45, or 55
4 sex is not 1 or 2

As near as I can tell, it is working. (I'm new at this, so don't laugh
at its inefficiency!) Here's my dilemma - I build the query in the
design grid and then did some tweaking in the SQL view. When I got the
query to the point at which you see it below and shifted back to the
design view, I couldn't find any reference to criteria 3. Is that
normal? I thought if there was something in the SQL view, that it
would also show up in the design view. Any help would be very much
appreciated.

Mike

DELETE working.[Station #], working.[Check County], working.Ager,
working.Date, working.[County Harvest], working.Sex, working.Age,
working.[Right Beam], working.[Left Beam], working.[Right Points],
working.[Left Points], working.RecordNumber, (working.Age)>=5 And
(working.Age)<=55 AS Expr1
FROM working
WHERE (((working.Sex) Is Null)) Or (((working.Age) Is Null)) Or
(((working.Sex) Not In (1,2))) Or ((((working.Age)>=5 And
(working.Age)<=55)=False));