Can't delete from specified tables on Unjoined Tables

[Dave]

I am also experiencing this. I get the date from and Unjoined Table that
only has a single record which is system generated.

Perhaps you can guide me as well.

Thanks

DELETE DISTINCTROW AllCalls.*, AllCalls.Check
FROM AllCalls, PeriodDates
WHERE (((AllCalls.Check)<DateValue([YearBegin])));

 

[Michel Walsh]

Non equi joins have updatability problems.

Try:

DELETE AllCalls.*
FROM AllCalls
WHERE AllCalls.Check < ANY( SELECT DateValue(YearBegin)
FROM PeriodDates)



or


DELETE AllCalls.*
FROM AllCalls
WHERE AllCalls.Check < ( SELECT DateValue(MAX(YearBegin))
FROM PeriodDates)

(the two formulations differ if there is NULL).

or something like that. Be sure you make your tests on a copy of data,.
since that delete can do massive modifications.



Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

All SQL solution
DELETE DISTINCTROW AllCalls.Check
FROM AllCalls
WHERE AllCalls.Check <
(SELECT DateValue(First([YearBegin]))
FROM PeriodDates)

Or use the VBA DLookup function

DELETE DISTINCTROW AllCalls.Check
FROM AllCalls
WHERE AllCalls.Check < DateValue(DLookup("YearBegin","PeriodDates"))


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Dave]

Thank you

DELETE AllCalls.*
FROM AllCalls
WHERE AllCalls.Check < ANY( SELECT DateValue(YearBegin)
FROM PeriodDates)


Worked like a charm

Dave

Non equi joins have updatability problems.

Try:

DELETE AllCalls.*
FROM AllCalls
WHERE AllCalls.Check < ANY( SELECT DateValue(YearBegin)
FROM PeriodDates)



or


DELETE AllCalls.*
FROM AllCalls
WHERE AllCalls.Check < ( SELECT DateValue(MAX(YearBegin))
FROM PeriodDates)

(the two formulations differ if there is NULL).

or something like that. Be sure you make your tests on a copy of data,.
since that delete can do massive modifications.



Hoping it may help,
Vanderghast, Access MVP

I am also experiencing this. I get the date from and Unjoined Table that
only has a single record which is system generated.

Perhaps you can guide me as well.

Thanks

DELETE DISTINCTROW AllCalls.*, AllCalls.Check
FROM AllCalls, PeriodDates
WHERE (((AllCalls.Check)<DateValue([YearBegin])));