Help with simple delete query

[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].

 

[John Spencer (MVP)]

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)

 

[Randal]

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")

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].