ACCESS FEED: Re: [MS_AccessPros] Delete query giving me error

Kamis, 14 Juli 2011

Re: [MS_AccessPros] Delete query giving me error

Crystal,

What I am trying to do is archive records in several tables. I have a Events
table and a tblEventDetails table. I was able to get the archive to work, in the
respect of appending the records into Archive tables. But now I am trying to
delete the records that have been archived.

They will be performing this archiving process at the end of each calendar year.
Is there a difference between what I last sent and your delete statement? I
found the answer in the help of the error dialogue box.

Jim Wagner
________________________________

________________________________
From: Crystal <strive4peace2008@yahoo.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thu, July 14, 2011 11:22:56 AM
Subject: Re: [MS_AccessPros] Delete query giving me error

Hi Jim,

when I have trouble with delete queries, I turn them into SELECT queries, select
all the records (Ctrl-A), and press DELETE.

Works for a one-time solution. For code, you sometimes have to nest queries or
loop through a recordset.

for what its worth, I took a quick look at your logic. Never use * without
qualifying it. And you don't need to specify the ID field. Try this:

DELETE tblEventDetails.*
FROM tblEventDetails
WHERE tblEventDetails.EventID IN
(SELECT EventID FROM tblArchivedEventIDs)

Warm Regards,
Crystal

*
(: have an awesome day :)
*

--- On Thu, 7/14/11, luvmymelody wrote:

> Hello All,
>
> I am getting the following error "Specifiy the table
> containing the records you want to delete".
>
> The following sql statement is based on the post number
> 68733. But I continue to get the error.
>
> DELETE tblEventDetails.EventID, *
> FROM tblEventDetails INNER JOIN tblArchivedEventIDs ON
> tblEventDetails.EventID = tblArchivedEventIDs.EventID
> WHERE
> (((tblEventDetails.EventID)=[tblArchivedEventIDs].[EventID]));
>
> I am trying to delete a set of records in a table based on
> values in a second table. I am surely missing something.
> Does anyone see the problems?
>
> Thank you for your help very much
>
> Jim Wagner
>
>

[Non-text portions of this message have been removed]