Unanswered: Academic Year as opposed to regular year help!
The database i am working on is for a university sports team where they like to keep track of things based on academic year/season (2007-2008, 2008-2009, 2009-2010, etc).
They have many different member types and one of them is Season Ticket Holder. They want to keep track of the season of their ticket and the seat number.
I am in a predicament because i don't know how to keep track of academic year and be able to run queries off it. For instance, they want a query/report for all members who are current season ticket holders. This would be simple if a season was just 2009 but it is 2008-2009.
the tricky part is that i can't base it on just the year matching (spring 2009 and fall 2009 are 2 separate seasons). They also only know whether a person is a season ticket holder for that season, not the specific date they became a season ticket holder.
All the queries for Current <anything> will be based on the Now() function since I am just creating a database for them and won't be able to update it at all.
I was thinking of having a FROM <DATE> and TO <DATE> field consisting of just years but don't think there is a way to check if they are in the current academic year using that method.
Has anyone else been in a similar situation for a university or a DB based on academic year? I'd appreciate any help.
I suppose you will also have to create some logic that would convert the a date into a season year. That way when you need to look up current season ticket holders the Now() function would return May 3rd, 2009 which you would convert to 2008 to use in your query.
You could try something like this in a standard code module:
'Desc: function to return the Academic year based on the passed in date
'Pre: pDate is a valid date
'Post: an integer is returned with the Academic year the passed in date falls within
Public Function AcademicYear(pDate As Date) As Integer
Dim Year As Integer
Year = DatePart("yyyy", pDate)
If DatePart("m", pDate) <= 6 Then
AcademicYear = Year - 1
AcademicYear = Year
Depending on how you store the Academic Year in your table you may have to change the return type of the function to match.
Then all you need to do use the function from within your query the return the Academic Year and you can then use whatever criteria against that you want.
Hope that makes sense. Let me know if you need more help getting the code going.