Complicated delete query

  • Thread starter Thread starter Ragnar Midtskogen
  • Start date Start date
R

Ragnar Midtskogen

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
 
How about something like this:

DELETE
FROM Alphadex
WHERE
NOT
((Nz(First & Last, "") <> "" OR Nz(Company, "") <> "")
AND
(Nz(Address1 & Address2 & City & State & Zip, "") <> "" OR Nz(Phone, "") <>
""))
 
Back
Top