Current Term Calculation in School Database

[Datatonic]

I have a database that manages some schools.

The term dates are the same for all schools.

I want to be able to calculate which term is the (current term) an
which term is the term that has just passed (last term).

The term table has the following fields

Term ID ID
TermSeason Spring, Summer, Autumn
TermYear Number field
TermStart Long Date
TermEnd Long Date


Last term is always the last term to have ended according to today’
date.

Current term can be one that has yet to start. Current Term is alway
the next term to end according to today’s date.

I am totally lost as to how to calculate this.

The current term and last term need to be displayed in various queries

 

[Dave C]

If the terms are entered in order so that the ID sorts them correctly (this
won't work on replicated ID) ,

LastTermID=DMax("TermID","Term","TermEnd < Date()")
CurrentTermID=DMin("TermID",Term","TermEnd>Date()")

Dave C.