All records with 12 months of Date()? (1 Viewer)

[jimtimber]

Hi,

We have a field (tbl_Enrolment.CourseCompletedOn) which holds the date a colleague completed a course.

I want to bring through all courses completed within 12 months of todays date ( Date() ) from our database. Is there an easy way of doing this?

I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.

Thank you for any help

Jim

 

[vbaInet]

Hi jimtimber

I checked google and it suggested YEAR but that would only bring through dates with the same year, not within a 12 month period.

Have a look again at the Year() function:

http://www.techonthenet.com/access/functions/date/year.php

 

[Minty]

Create a query with CourseCompletedOn where >date()-365 is in the criteria is probably the simplest method (If not 100% accurate in a leap year).

If not you can use DatePart() or DateAdd() for a more controllable solution if you want all of the current months - there are lots of examples on here.

 

[pr2-eugin]

Something like,

SELECT tbl_Enrolment.courseID, tbl_Enrolment.colleagueName, tbl_Enrolment.CourseCompletedOn
FROM tbl_Enrolment
WHERE tbl_Enrolment.CourseCompletedOn BETWEEN [URL="http://www.techonthenet.com/access/functions/date/dateadd.php"]DateAdd[/URL]("m", -12, Date()) And Date();

 

[jimtimber]

That's awesome, thank you all. Both pr2-eugin and Minty's ideas work.