discrepancy between SQL and Design view

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tonk - 28 Dec 2006 19:38 GMT

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));

Reply to this Message

John Vinson - 29 Dec 2006 02:55 GMT

>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]

Reply to this Message

Tonk - 29 Dec 2006 12:11 GMT

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

> >1) sex is null
> >2) age is null
[quoted text clipped - 9 lines]
>
> John W. Vinson[MVP]

Reply to this Message

David F Cox - 29 Dec 2006 14:34 GMT

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:
[quoted text clipped - 24 lines]
> (((working.Sex) Not In (1,2))) Or ((((working.Age)>=5 And
> (working.Age)<=55)=False));

Reply to this Message

John Nurick - 30 Dec 2006 08:19 GMT

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:
[quoted text clipped - 24 lines]
>(((working.Sex) Not In (1,2))) Or ((((working.Age)>=5 And
>(working.Age)<=55)=False));

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message