I have a table that contains records of employees that have left our company. One of the fields is called End Date and this has a date in the format dd/mm/yy that states what date the employee left. It is all based on a table called employees. The form is called Emp Left.

I want to create a delete query that will delete records 6 months after the end date (so their details are not on file too long).
What expression do I put as the criteria in the delete query , for the end date field, to express " exactly 6 months after x date" with x being the value of that field.

Help much appreciated.

Scott.

i would put [endDate] + 180 < Date() in the criteria

Scott,

to select exactly on six months, try this:

......
Where Format(Date()-YourTable.[End Date],"m")=6
AND DatePart("d",Date())=DatePart("d",YourTable.[End Date])


HTH,

RV

Happy YN,

I don't think this will work because it is saying to just add 180, not 180 days. The format of the field is dd/mm/yy, so I don't think you can just put 180. I tried it and it said is was invalid syntax.

RV,

Your way looks a little more workable. Now, do I copy and paste the code you stated into the criteria bit of the query when in design view? I have tried copying it straight after changing names, but it is saying invalid syntax.

Here is my version of it:

Where Format(Date()-Employee Archive.[End Work],"m")=6
AND DatePart("d",Date())=DatePart("d",Employee Archive.[End Work])

Any ideas?

Copy the WHERE clause in your query in SQL view, not in design view.

And the solution of Happy YN will indeed add 180 days !!!

Greetings,

RV

Currently my SQL looks like this:

DELETE [Employees Archive].*, [Employees Archive].[Employee ID], [Employees Archive].[Employee Name], [Employees Archive].[Address 1], [Employees Archive].[Address 2], [Employees Archive].[Address 3], [Employees Archive].[Post Code], [Employees Archive].[Telephone Home], [Employees Archive].[Telephone Mobile], [Employees Archive].Gender, [Employees Archive].[Driver?], [Employees Archive].DOB, [Employees Archive].[Work Status], [Employees Archive].[Started Work], [Employees Archive].[End Probation], [Employees Archive].[End Work], [Employees Archive].[Police Check], [Employees Archive].[Ref 1], [Employees Archive].[Ref 2], [Employees Archive].[Ref 3], [Employees Archive].[Unit ID], [Employees Archive].[Job ID], [Employees Archive].[Holiday Due], [Employees Archive].[Holiday Taken], [Employees Archive].[Holiday Left], [Employees Archive].[Other Notes]
FROM [Employees Archive];

Where do I put your expression? Can you stick it in form me?

Many Thanks

Scott.

Apologies go to Happy YN, you way did work.

Thanks all.

Scott.

Scott,

just add the WHERE clause at the end of your statement in SQL view.
Thus your statement becomes:

DELETE [Employees Archive].*, [Employees Archive].[Employee ID], [Employees Archive].[Employee Name], [Employees Archive].[Address 1], [Employees Archive].[Address 2], [Employees Archive].[Address 3], [Employees Archive].[Post Code], [Employees Archive].[Telephone Home], [Employees Archive].[Telephone Mobile], [Employees Archive].Gender, [Employees Archive].[Driver?], [Employees Archive].DOB, [Employees Archive].[Work Status], [Employees Archive].[Started Work], [Employees Archive].[End Probation], [Employees Archive].[End Work], [Employees Archive].[Police Check], [Employees Archive].[Ref 1], [Employees Archive].[Ref 2], [Employees Archive].[Ref 3], [Employees Archive].[Unit ID], [Employees Archive].[Job ID], [Employees Archive].[Holiday Due], [Employees Archive].[Holiday Taken], [Employees Archive].[Holiday Left], [Employees Archive].[Other Notes]
FROM [Employees Archive]
WHERE Format(Date()-Employee Archive.[End Work],"m")=6
AND DatePart("d",Date())=DatePart("d",Employee Archive.[End Work]);

Greetings,

RV