Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    7

    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.

    Thanks,

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Let me ask a question: So does one season always span Fall to Spring? If that is the case then you should be able to just store the Fall year (ie. 2008 for Fall 2008 through Spring 2009) .

    ...or did I completely misread your post as I have a tendency to do that.

    C

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    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.

    C

  4. #4
    Join Date
    May 2009
    Posts
    7
    I never thought of that but it sounds like it would work

    How would you go about doing that?

    would you have to parse the month and day part from date that it returns?

    ex)

    if 3-5-2009 is what is returned from Now()

    you would set a variable equal to 5 (the current month) and if it is < 6 (when the semester ends) then the current year equals current year - 1. Else year = year


    Or can this not be done in access like i have said?

  5. #5
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You could try something like this in a standard code module:

    Code:
    '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
        Else
            AcademicYear = Year
        End If
    End Function
    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.

    C

  6. #6
    Join Date
    May 2009
    Posts
    7
    I'm having trouble getting the code to go through. I created it under Modules with the name AcademicYear.

    I created a table [Current Date] with a field also called [Current Date] that stores the value of the Now() function. It is displayed as 5/3/2009 2:29:08 PM.

    The SQL for the query is:
    SELECT AcademicYear([Current Date]) FROM [Current Date];

    However when i try to run I get 'undefined function 'AcademicYear' in expression' error message.

    Did i do something wrong or miss something? Thanks.

  7. #7
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Careful how you name the code module...don't name it the same as the function. For instance, name the code module "modStandard" and the function "AcademicYear".

    Also, I wouldn't have a table and a field name exactly the same either.

    That should clear up the confusion.

    And for that matter why would you need a table just to store the Now() function. All that can be done from inside a query. Something like:

    SELECT * FROM tblTicketHolders WHERE TicketYear = AcademicYear(Now())

    That would select all the ticket holders from a table named tblTicketHolders where the season they bought the tickets for matches the current academic season.

    C

  8. #8
    Join Date
    May 2009
    Posts
    7
    Thanks! it works perfectly now. =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •