delete query not working

[macca007]

strSQL ="Delete * from Patients " & _
WHERE Treatment.[treatdate] Not Between #01/01/1999# And #12/31/1999#;

docmd.runsql strSQL

Hi the above code is not working i would like to delete all the data in patients table based on criteria on treatment tables treatdate.

Patient is the primary table has relationship of one to many.

got all my referential integrity to work but the criteria field is based in treatment table which is not the primary table.

any suggestions?

any suggestions

 

[Crowley16]

get rid of the *

DELETE FROM tblName WHERE...;

and make sure you've got "" around the entire sql string

 

[PHV]

Your problem is that in the where clause you reference another table (Treatment)
Have you tried to create your query in the query design window and then grab the generated sql code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Fizban99]

Hmm. The best I can come up with is:

DELETE FROM Patients WHERE EXISTS
( SELECT TreatDate, PatientID FROM tblTreatment WHERE (TreatDate Not Between #01/01/1999# And #12/31/1999#) AND (Patients.PatientID = Treatment.PatientID) );

This almost works, except that if a patient has no record in the Treatment table, they won't get deleted. I assume that's not what you want, and you could always run a second query to get rid of those patients, but it seems there must be a way to do it all in one step.

Anyone else see a better solution?


Andrew

 

[PHV]

Something like this ?
DELETE FROM Patients WHERE PatientID NOT IN
(SELECT DISTINCT PatientID FROM tblTreatment WHERE Year(TreatDate)=1999);


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244