Complicated delete query
From: Ragnar Midtskogen (ragnar_ng_at_optonline.net)
Date: 04/07/04
- Next message: Zac: "Complie error using Dateserial function"
- Previous message: Manuel: "Re: 3rd Business Day"
- Next in thread: Brian Camire: "Re: Complicated delete query"
- Reply: Brian Camire: "Re: Complicated delete query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 7 Apr 2004 12:46:14 -0400
Hello,
I have an application with a table used as an address book.
The table has fields for name, address, phone and company name.
Over the years the client has entered many incomplete records so he asked if
I would clean up the table.
What should be left in the table are records which have at least:
name and address
name and phone
company name and address
company name and phone
I think what I need to do is to select for deletion those records which only
has name or address or phone or company name, but the query below is the
only one I can come up with. It works, but it just seems needlessly
complicated.
DELETE *.*
FROM Alphadex
WHERE (
(
(((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
AND
(((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
AND
(((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
AND
(((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
AND
(((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
AND
(((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
AND
(((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
AND
(((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
)OR
(
(((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
AND
(((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
AND
(((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
AND
(((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
AND
(((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
AND
(((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
AND
(((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
AND
(((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
)
OR
(
(((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
AND
(((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
AND
(((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
AND
(((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
)
OR
(
(((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
AND
(((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
AND
(((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
AND
(((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
AND
(((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
AND
(((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
AND
(((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
AND
(((Alphadex.Company) Is Null) Or ((Alphadex.Company)=""))
)
OR
(
(((Alphadex.First) Is Null) Or ((Alphadex.First)=""))
AND
(((Alphadex.Last) Is Null) Or ((Alphadex.Last)=""))
AND
(((Alphadex.Address1) Is Null) Or ((Alphadex.Address1)=""))
AND
(((Alphadex.Address2) Is Null) Or ((Alphadex.Address2)=""))
AND
(((Alphadex.City) Is Null) Or ((Alphadex.City)=""))
AND
(((Alphadex.State) Is Null) Or ((Alphadex.State)=""))
AND
(((Alphadex.Zip) Is Null) Or ((Alphadex.Zip)=""))
AND
(((Alphadex.Phone) Is Null) Or ((Alphadex.Phone)=""))
)
);
BTW, note that all fields allow zero length string, so I can not just check
for Null.
Any help would be appreciated.
Ragnar
- Next message: Zac: "Complie error using Dateserial function"
- Previous message: Manuel: "Re: 3rd Business Day"
- Next in thread: Brian Camire: "Re: Complicated delete query"
- Reply: Brian Camire: "Re: Complicated delete query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
