how do i change sql statements in visual basic?

[Christam1]

I am creating a database system (In Access) for a school, which contains the students and their progress in exams etc.

I have four select queries, 'Year 1' 'Year 2' 'Year 3' Year 4', which each show all students in those year groups from a root table based on student details - (criteria has been added into the 'Date of Birth' field in these queries so that they only show students born between two dates). This is all working fine.

The problem is that when the end of the school year comes, each year group will become one higher than what it was the previous year.

Is there a way to create a macro/module in visual basic that changes the sql statement so that the year groups all move up into the appropriate queries (i.e. the current 'Year 1' will be next year's 'Year 2' and so on)?

Or if not, does anyone know a different method of sorting my problem out?

[MAK]

In your query compare today's date with date joined to determine the results.

for example.
year4 query should look like below

select * from table1 where select datediff("yyyy",DateJoined,now()) = 4