Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1

    Unanswered: filter query by dates since start of current school year

    What I want to see is a list of all the times a student has been absent or tardy in the current school year. I have a table that has the dates of when each student has been either absent or tardy. I can make a query and then filter by date since 9-1-14, but then next year I will have to go in and change that to 9-1-15. Is there anyway to make this automatic? Thanks!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Make a form with a text box for the date.
    The query uses this as its limit...
    Select * from table where [date]> forms!frmRpts!txtDate

  3. #3
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    I'm not sure I entirely understand, but won't that involve typing the date each time? I'm trying to avoid that as well.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the classic way of handing this sort of thing is to have a calendar table with dates in.
    as part of that calendar definition define your academic terms and periods & years.
    then get a user to select the academic year, and or period and or whatever.
    or based on the current system date extract data for a specifc year
    say your calendar table was called ACYears
    AcYear 'academic year
    AcPeriod 'a week number within that academic year
    StartsOn
    EndsOn
    eg:
    2014/15; 1; 01/09/2014; 31/08/2015
    2013/14; 1; 03/09/2013; 31/08/2014


    then you can always find the current academic year by joining your other tables to this table and limit this table (using a where clause)
    you want rows from other tables (so join on the date columns

    select my, column, list from mytable,AcYears
    left join mytable on AcYears.StartsOn<= mytable.adatecolumn and AcYears.EndsOn >= mytable.adatecolumn
    where AcYears.StartsOn<= date() and AcYears.EndsOn >= date()

    ..the join defines the rows to be returned, the where limits this to the current academic year

    ..theres almost certainly some error in the above, but it shoudl give you a pointer as to how to develop a solution.

    you may need to join using a sub select
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2004
    Posts
    1

    filter query by dates since start of current school year

    You could do this in the query:

    Expr1: Format([Date Field],"yyyy")

    And put this in the criteria of the field:

    Year(Date())

    Of course you will need to replace [Date Field] with your own date field. what this does is to format your date field to show only the year and the criteria filters for only the current year.

    HTH

    - Charles

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    which would work if the academic year ties in with the calendar year......
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2014
    Posts
    34
    Provided Answers: 1
    Got it, thanks! I already had a School Year table so I could apply payments to this year or last year or next year or whatever, so I just added a start date and end date column. Now I just had to make the class date > the start date of the current school year and < the end date. Great! Thanks!

Posting Permissions

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