Need help with a date related query

[Bloberoonie]

Hi Everyone,



Brand new to access and need a bit of help. I run a business where a person takes a training course [date Taken] and then has an [valid to] date. What I need to do is to be able to run a query that will tell me two months in advance, all the candidates whos training needs renewing. Some courses are valid for 1 year others 3 so I'm not sure if I need to include another field for this or if the query will work just becuse of the [valid to] field.

I have tried to create the criteria/expression but to no avail. Can anyone help with this?

Steve

[alansidman]

Here is one solution that will tell you how may days to expiration.

SELECT Table1.[valid to], DateDiff('d',Date(),[valid to]) AS [Days to Expiry]
FROM Table1;

Change the table name to yours. As a word of advise in field names, I would avoid using spaces. It may be a thorn in your side in the future. Prefer to use name ie. ValidTo and DateTaken. Same for Table Names

You can then put this in the criteria for the Days to Expiry Field: < 60

[Bloberoonie]

Hi,

Many thanks for the answer, can I just ask where I put the code? i.e in the query criteria or in an expression?

My table name is [course candidates]

Thank you

[alansidman]

That is a SQL Statement. You would open the query in Design view and then switch to SQL View. Copy the code into the SQL statement using your table name. Then switch back to design mode and edit the query as necessary (ie. add additional fields if needed, etc.)

[Bloberoonie]

Thank you so much! that did it!