Re: help with delete query
From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 04/30/04
- Next message: Bill: "Urgent Update Query"
- Previous message: Sam D: "Re: rounding a number in a query"
- In reply to: Allen Clark: "help with delete query"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 29 Apr 2004 20:49:19 -0400
First, I would use field Is Null as my criteria.
DELETE Persons.*
FROM Persons LEFT JOIN EMail
ON Persons.id = EMail.Id
WHERE [persons].[first] Is Null AND
[persons].[last] Is Null AND
[persons].[zip] is Null AND
[email].[emailaddr] is Null
Second, I would check that the SQL was as above and not
DELETE * ...
or
DELETE Persons.*, * ...
Those two statements both have a problem in that they refer to more than one table.
Allen Clark wrote:
>
> I have an Access 2000 db with many linked tables. I have determined that if
> the persons.first, persons.last, persons.zip, and email.emailaddr fields are
> all blank, the record was incorrectly entered and I would like to get rid of
> any record that matches these criteria. When I build the delete query, the
> SQL that is generated is as follows:
>
> DELETE Persons.*
> FROM Persons LEFT JOIN EMail ON Persons.id = EMail.Id
> WHERE (((IsNull([persons].[first]))<>False) AND
> ((IsNull([persons].[last]))<>False) AND ((IsNull([persons].[zip]))<>False)
> AND ((IsNull([email].[emailaddr]))<>False));
>
> If this same selection criteria is used in a simple query with a left outer
> join, and select only the Persons.ID field, I get a listing of 1676 records.
> If I convert it to a delete query, the only message I get is "could not
> delete from specified tables".
>
> Any ideas on what is going on here?
>
> Thanks in advance,
> Allen
- Next message: Bill: "Urgent Update Query"
- Previous message: Sam D: "Re: rounding a number in a query"
- In reply to: Allen Clark: "help with delete query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
