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 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.
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.
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)
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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |