Can't delete from specified tables on Unjoined Tables

  • Thread starter Thread starter Dave
  • Start date Start date
D

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])));
 
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
 
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
 
Thank you

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


Worked like a charm

Dave

Michel Walsh said:
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



Dave said:
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])));
Click to expand...
Click to expand...
 
Back
Top