Date comparisons

[scoe]

Hi, hope someone can help... I am trying to pull out when students were in their final year at school. At present I have all the dates when they hit 16 but I really need to know if they were 16 between 01/09 and 31/08 of that year, if not then they would fall in the previous year or the following year and then compare this with the date they took their qualifications and then allocate a code depending on if they took the qualification in their final year or before or if they have taken it since.



I hope I have put this clear enough and if so any thoughts would be appreciated.

[ranman256]

in the query:
[Birthdate] between 01/09/yy and 31/08/yy

[scoe]

Thank you, I've used this to establish their age and I have the date of their 16th birthday but I need to then compare this date against the award date and query out those that have taken the award during their final year.... if that makes sense.

[June7]

You show dates as international, might review http://allenbrowne.com/ser-36.html

Showing dates as U.S./Access structure - Maybe:
SELECT StudentID, DateAdd("yyyy",16,[Birthdate]) AS BD16, Year([BD16])+IIf([BD16]>"8/31/" & Year([BD16]),1,0) AS FinalYr, Year([QualificationsDate]) AS QualYr, IIf(FinalYr=QualYr,"A","B") AS Code;

[scoe]

Thank you, I'll try that.... At present I have got round it with a union query looking at years separately but this means a lot of work each year to alter the queries.