Date Query Problem

[expublish]

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.

 

[Happy YN]

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

 

[RV]

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

 

[expublish]

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?

 

[RV]

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

 

[expublish]

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.

 

[expublish]

Apologies go to Happy YN, you way did work.

Thanks all.

Scott.

 

[RV]

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