Help with simple delete query

  • Thread starter Thread starter Randal
  • Start date Start date
R

Randal

The following query returns the error message "Could not delete from
specified tables."

DELETE [Table1].[Date], Table1.*
FROM [Table1], [Table2]
WHERE ((([Table1].[Date]) Not Between [Table2]![BeginDate] And
[Table2]![EndDate]));

I need to delete records from Table 1 that fall outside the date range that
exists as a single record in table 2. Table 2 will never have more than one
record with the 2 fields [BeginDate] And [EndDate].
 
TRY

DELETE DISTINCTROW [Table1].[Date]
FROM [Table1], [Table2]
WHERE ((([Table1].[Date]) Not Between [Table2]![BeginDate] And
[Table2]![EndDate]));

I think that will still fail, but the following should work for you

DELETE Table1.*
FROM [Table1]
WHERE [Table1].[Date]) Not
(Between DLookup("BeginDate","Table2") and DLookup("EndDate,"Table2"))

OR

DELETE Table1.*
FROM [Table1]
WHERE [Table1].[Date] < DLookup("BeginDate","Table2") OR
[Table1].[Date] > DLookup("EndDate,"Table2")

(NOTE: you may still need the DISTINCTROW Predicate in the above statement to
ensure deletion)
 
Thanks
This worked but I had to use [] around field names in DLookup.

DELETE Table1.*
FROM [Table1]
WHERE [Table1].[Date] < DLookup("[BeginDate]","Table2") OR
[Table1].[Date] > DLookup("[EndDate]",Table2")

John Spencer (MVP) said:
TRY

DELETE DISTINCTROW [Table1].[Date]
FROM [Table1], [Table2]
WHERE ((([Table1].[Date]) Not Between [Table2]![BeginDate] And
[Table2]![EndDate]));

I think that will still fail, but the following should work for you

DELETE Table1.*
FROM [Table1]
WHERE [Table1].[Date]) Not
(Between DLookup("BeginDate","Table2") and DLookup("EndDate,"Table2"))

OR

DELETE Table1.*
FROM [Table1]
WHERE [Table1].[Date] < DLookup("BeginDate","Table2") OR
[Table1].[Date] > DLookup("EndDate,"Table2")

(NOTE: you may still need the DISTINCTROW Predicate in the above statement to
ensure deletion)
The following query returns the error message "Could not delete from
specified tables."

DELETE [Table1].[Date], Table1.*
FROM [Table1], [Table2]
WHERE ((([Table1].[Date]) Not Between [Table2]![BeginDate] And
[Table2]![EndDate]));

I need to delete records from Table 1 that fall outside the date range that
exists as a single record in table 2. Table 2 will never have more than one
record with the 2 fields [BeginDate] And [EndDate].
Click to expand...
Click to expand...
 
Back
Top