Problems deleting records using a Delete Query 1

[rcoutts]

I have a Query that when I specify it to be a Select Query, selects the records I wish to delete from a local Table. But, when I convert it to a Delete Query and run it, I get the message:

     "You are about to run a delete query that will modify data in your table"

With is looking good, but when I click "Yes" I get the message:

     "Could not delete from specified tables."

What I'm doing seems pretty straight forward. Can someone clue me in where I may have gone wrong?

Thanks!
Rich

 

[nomus1]

It sounds like your query may be deleting from more than one table. Could you include the SQL statement?

 

[rcoutts]

My SQL code is

    DELETE tblJobHours.*, tblJobHours.WeekEnding
    FROM tblJobHours, tblTimeCard
    WHERE (((tblJobHours.WeekEnding)<[tblTimeCard]![WeekEnding]-35));
[\code]
I want to delete records that are 35 days older than the field tblJobHours.WeekEnding. I don't know SQL, but it looks like you're right, that its trying to delete fields from tblTimeCard, which isn't what I want.  In the Design View, I have the tblJobHours and the tblTimeCard visable.  The parameters look like this:
[code]
      Field: tblJobHours.*   WeekEnding
      Table: tblJobHours     tblJobHours
     Delete: From            Where
   Criteria:                 <[tblTimeCard]![WeekEnding]
[\code]
If I remove the references to tblTimeCard, then the Query prompts me for a value for tblTimeCard!WeekEnding, which isn't what I want.

Any suggestions?
Thanks,
Rich

 

[nomus1]

Try changing your SQL to

Try

Field: tblJobHours.* WeekEnding
Table: tblJobHours TblTimeCard
Delete: From Where
Criteria: <35


DELETE tblJobHours.* FROM tblJobHours inner join
tblTimeCard on tblJobHours.weekending = tblTimeCard.weekending WHERE (((tbltimecard.WeekEnding)<35));

Let me know if this works

 

[rcoutts]

Thanks for the code. Unfortunately it selected &quot;0 Rows&quot; for deletion -- could it be that the WHERE statement doesn't select records that are 35 days older than the field tblTimeCard.WeekEnding?

I did find a work around, however. tblTimeCard.WeekEnding has the newest time card date, but as you pointed out was causing trouble because my query was trying to delete from it. So, I got rid of the reference and instead search through tblJobHours and get the newest record entered and then delete all records that are 35 days older than it (the real goal of my Query is just to allow users to look through old time cards, but to clean out ones that are a month or so old.)

FYI, my code now looks like this:

DELETE tblJobHours.*, tblJobHours.WeekEnding
FROM tblJobHours
WHERE (((tblJobHours.WeekEnding)<DMax(&quot;[WeekEnding]&quot;,&quot;tblJobHours&quot;)-35));
[\code]
Thanks so much for you help!
Rich