424,066 Members | 2,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,066 IT Pros & Developers. It's quick & easy.

Selective deletion - delete records based on dates

P: n/a

Dear all,

I'm trying to do a selective deletion from an 'Archive' table based on
dates.

Here is the setup:

Table 'Archive' has a date field ( [DateArchive] ).
Another table ('Cutoffs') defines the Low and High Cut-off dates
(fields: [DateCutoff_Low], [DateCutoff_Hi] respectively).

The logic is:

We want to delete all the records from the Archive table where:
[DateArchive] >= [DateCutoff_Low] OR [DateArchive] <=
[DateCutoff_Hi]

The closest I've got (using the Access GUI) generates this SQL:

DELETE
Archive.*, Archive.DateArchive
FROM
Archive, Cutoffs
WHERE
(((Archive.DateArchive)>=[Cutoffs]![DateCutOff_Low]
Or
(Archive.DateArchive)<=[Cutoffs]![Date_CutOff_Hi]));

This generates a 'Could not delete from specified tables' error.

Any insights for this?

Many thanks,
Alex

Feb 23 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Feb 23, 8:02 am, "alex.hatzisavas" <alex.hatzisa...@gmail.com>
wrote:
Dear all,

I'm trying to do a selective deletion from an 'Archive' table based on
dates.

Here is the setup:

Table 'Archive' has a date field ( [DateArchive] ).
Another table ('Cutoffs') defines the Low and High Cut-off dates
(fields: [DateCutoff_Low], [DateCutoff_Hi] respectively).

The logic is:

We want to delete all the records from the Archive table where:
[DateArchive] >= [DateCutoff_Low] OR [DateArchive] <=
[DateCutoff_Hi]

The closest I've got (using the Access GUI) generates this SQL:

DELETE
Archive.*, Archive.DateArchive
FROM
Archive, Cutoffs
WHERE
(((Archive.DateArchive)>=[Cutoffs]![DateCutOff_Low]
Or
(Archive.DateArchive)<=[Cutoffs]![Date_CutOff_Hi]));

This generates a 'Could not delete from specified tables' error.

Any insights for this?

Many thanks,
Alex

Check your syntax for field name consistency.

Feb 24 '07 #2

P: n/a
On Feb 24, 6:55 pm, lgeastw...@gmail.com wrote:
On Feb 23, 8:02 am, "alex.hatzisavas" <alex.hatzisa...@gmail.com>
wrote:
Dear all,
I'm trying to do a selective deletion from an 'Archive' table based on
dates.
Here is the setup:
Table 'Archive' has a date field ( [DateArchive] ).
Another table ('Cutoffs') defines the Low and High Cut-off dates
(fields: [DateCutoff_Low], [DateCutoff_Hi] respectively).
The logic is:
We want to delete all the records from the Archive table where:
[DateArchive] >= [DateCutoff_Low] OR [DateArchive] <=
[DateCutoff_Hi]
The closest I've got (using the Access GUI) generates this SQL:
DELETE
Archive.*, Archive.DateArchive
FROM
Archive, Cutoffs
WHERE
(((Archive.DateArchive)>=[Cutoffs]![DateCutOff_Low]
Or
(Archive.DateArchive)<=[Cutoffs]![Date_CutOff_Hi]));
This generates a 'Could not delete from specified tables' error.
Any insights for this?
Many thanks,
Alex

Check your syntax for field name consistency.

The above is just an example to demonstrate the logic, it does not
include the real field names.
Anyhow i believe this has nothing to do with field syntax, but is
related to how Jet SQL handles selective deletions.

Feb 25 '07 #3

P: n/a
On Feb 25, 10:58 am, "alex.hatzisavas" <alex.hatzisa...@gmail.com>
wrote:
On Feb 24, 6:55 pm, lgeastw...@gmail.com wrote:


On Feb 23, 8:02 am, "alex.hatzisavas" <alex.hatzisa...@gmail.com>
wrote:
Dear all,
I'm trying to do a selective deletion from an 'Archive' table based on
dates.
Here is the setup:
Table 'Archive' has a date field ( [DateArchive] ).
Another table ('Cutoffs') defines the Low and High Cut-off dates
(fields: [DateCutoff_Low], [DateCutoff_Hi] respectively).
The logic is:
We want to delete all the records from the Archive table where:
[DateArchive] >= [DateCutoff_Low] OR [DateArchive] <=
[DateCutoff_Hi]
The closest I've got (using the Access GUI) generates this SQL:
DELETE
Archive.*, Archive.DateArchive
FROM
Archive, Cutoffs
WHERE
(((Archive.DateArchive)>=[Cutoffs]![DateCutOff_Low]
Or
(Archive.DateArchive)<=[Cutoffs]![Date_CutOff_Hi]));
This generates a 'Could not delete from specified tables' error.
Any insights for this?
Many thanks,
Alex
Check your syntax for field name consistency.

The above is just an example to demonstrate the logic, it does not
include the real field names.
Anyhow i believe this has nothing to do with field syntax, but is
related to how Jet SQL handles selective deletions.- Hide quoted text -

- Show quoted text -
Ok the trick is to use an SQL Select statement in the Criteria of the
Delete query, for example as follows:
>=(SELECT tbl_000b_Date_Cutoffs.Date_CutOff_Low FROM tbl_000b_Date_Cutoffs)
Or
<=(SELECT tbl_000b_Date_Cutoffs.Date_CutOff_Hi FROM
tbl_000b_Date_Cutoffs)
Feb 26 '07 #4

P: n/a
On Feb 25, 10:58 am, "alex.hatzisavas" <alex.hatzisa...@gmail.com>
wrote:
On Feb 24, 6:55 pm, lgeastw...@gmail.com wrote:


On Feb 23, 8:02 am, "alex.hatzisavas" <alex.hatzisa...@gmail.com>
wrote:
Dear all,
I'm trying to do a selective deletion from an 'Archive' table based on
dates.
Here is the setup:
Table 'Archive' has a date field ( [DateArchive] ).
Another table ('Cutoffs') defines the Low and High Cut-off dates
(fields: [DateCutoff_Low], [DateCutoff_Hi] respectively).
The logic is:
We want to delete all the records from the Archive table where:
[DateArchive] >= [DateCutoff_Low] OR [DateArchive] <=
[DateCutoff_Hi]
The closest I've got (using the Access GUI) generates this SQL:
DELETE
Archive.*, Archive.DateArchive
FROM
Archive, Cutoffs
WHERE
(((Archive.DateArchive)>=[Cutoffs]![DateCutOff_Low]
Or
(Archive.DateArchive)<=[Cutoffs]![Date_CutOff_Hi]));
This generates a 'Could not delete from specified tables' error.
Any insights for this?
Many thanks,
Alex
Check your syntax for field name consistency.

The above is just an example to demonstrate the logic, it does not
include the real field names.
Anyhow i believe this has nothing to do with field syntax, but is
related to how Jet SQL handles selective deletions.- Hide quoted text -

- Show quoted text -
Hi folks

Try

Between DateCutoff_Low And DateCutoff_Hi

in the criteria! Usually works for me!

Feb 27 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.