473,320 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Selective deletion - delete records based on dates


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
4 3134
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Tuhin Kumar | last post by:
Hi, I have a requirement on improving the deletion rate on on records of a table. The table contains 5 million records, but since deleting everything matching the condition at one go was giving...
2
by: Peter yeshew | last post by:
I have 2 tables, Orders and Order Details, the same as with the database Nortwindtraders shipped with Access. The table order is linked with the table Order Details through the filed OrderID. It...
4
by: John Baker | last post by:
HI again: I have a query which selects records on tblPOmods on the basis of information on two other tables. I wish to delete the selected transactions, and am having a problem doing it. It...
2
by: Tim Tabor | last post by:
How can I delete records from a table with a criterion that depends on a value in an *unrelated* table? In other words, something like: DELETE tblPortfolioHistory.* FROM tblPortfolioHistory...
16
by: Lyle Fairfield | last post by:
There is an MS-SQL table named Bugs_Comments_and_Suggestions. There is a form named Bugs_Comments_and_Suggestions. To allow John Doe to use this form, we GRANT him LOGIN and ACCESS permissions...
2
by: Cornelius Buschka | last post by:
Hi, we saw the following problem: We deleted all rows from a table B referencing table A (~500000 records). No problem, but the following try to delete all records from table A (~180000) lead...
5
by: MRounds | last post by:
Hi Have looked through many forums but cannot find a solution for this. I have a table where i store certain dates and i want to use this table in several queries to delete records from multiple...
3
by: brucedodds | last post by:
My application has a form based on a parent table with a subform based on a child table. The relationship is Cascade Delete. The first record displayed when the form opens has five child records....
7
by: jmstur2 | last post by:
I have a table with what I consider duplicate records. Data in all columns are duplicate except for the date column, meaning that duplicate data was entered on different dates and those dates were...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.