Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Figuring Current School Year (Access 2000)

    Hello again I am working on a database that has students in it that have been referred to my office for truancy. Since the students are grouped by the school year, is it possible to create a function that can automatically know what the school year is?

    For example I have many reports and queries that have a parameter box asking the user for the school year, in which they would have to type in "2003-2004" to be able to view all of the students within that year. I only use the reports for the current school year. I used to just put in the criteria box of the query the school year and then I wouldn't have to type it every time I needed to run the query, but I don't want the users to go into design mode to have to do that at the begining of every year.

    I was hoping that a function could be done to figure the school year (which runs from August to June) I know how to do a little bit of code but not enough for this.......... ANY HELP WOULD BE VERY APPRICIATIVE!!!!!!!

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Figuring Current School Year (Access 2000)

    If you literally have 2001-2002, 2002-2003 and 2003-2004 etc. as text, you could use this as criteria to automatically select the current school year, assuming it is from September 1 through August 31:

    (Year(Date())+(Month(Date())<9)) & "-" & (Year(Date())+1+(Month(Date())<9))

    If otherwise, please post back, with details.

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Figuring Current School Year (Access 2000)

    Thank you Hans for responding so quickly... In the table truancy Data there is a text field that is called SchoolYR and it does literally hold values that say 2001-2002 etc.

    In my query I tried to use the expression you listed but it says data type mismatch in criteria..... Am I doing this wrong??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Figuring Current School Year (Access 2000)

    Hmm, it works for me. Would you please do the following:
    - Open the query (with the non-functioning criteria) in design view.
    - Use the View menu to switch to SQL view.
    - Copy the SQL string to the clipboard (Ctrl+C).
    - Paste it into a reply.
    Thanks.

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Figuring Current School Year (Access 2000)

    Ok here is the query without the parameter box for the school year:

    SELECT TruancyData.RespondentID, TruancyData.[#Absences], TruancyData.CodeID, TruancyData.StudentID, TruancyData.[Check Box], TruancyData.School, [tbl-Respondent Biographical Information].Birthdate, [tbl-Respondent Biographical Information].Race, IIf([sex]="1","M","F") AS Gender, TruancyData.DiversionDate, TruancyData.Notes, TruancyData.Action, TruancyData.SchoolYear, TruancyData.DateRec, TruancyData.JO, TruancyData.SRS, [tbl-Respondent Biographical Information].LastName, [tbl-Respondent Biographical Information].FirstName
    FROM [tbl-Respondent Biographical Information] INNER JOIN TruancyData ON [tbl-Respondent Biographical Information].RespondentID = TruancyData.RespondentID
    WHERE (((TruancyData.[Check Box])=Yes) AND ((TruancyData.JO)=No))
    ORDER BY [tbl-Respondent Biographical Information].LastName, [tbl-Respondent Biographical Information].FirstName;

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Figuring Current School Year (Access 2000)

    If you paste the following SQL string into the SQL view, and try to switch to datasheet view, do you still get the error message?

    SELECT TruancyData.RespondentID, TruancyData.[#Absences], TruancyData.CodeID, TruancyData.StudentID, TruancyData.[Check Box], TruancyData.School, [tbl-Respondent Biographical Information].Birthdate, [tbl-Respondent Biographical Information].Race, IIf([sex]="1","M","F") AS Gender, TruancyData.DiversionDate, TruancyData.Notes, TruancyData.Action, TruancyData.SchoolYear, TruancyData.DateRec, TruancyData.JO, TruancyData.SRS, [tbl-Respondent Biographical Information].LastName, [tbl-Respondent Biographical Information].FirstName
    FROM [tbl-Respondent Biographical Information] INNER JOIN TruancyData ON [tbl-Respondent Biographical Information].RespondentID = TruancyData.RespondentID
    WHERE ((TruancyData.[Check Box]=Yes) AND (TruancyData.JO=No) And (TruancyData.SchoolYear=(Year(Date())+(Month(Date( ))<9)) & "-" & (Year(Date())+1+(Month(Date())<9))))
    ORDER BY [tbl-Respondent Biographical Information].LastName, [tbl-Respondent Biographical Information].FirstName;

  7. #7
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Figuring Current School Year (Access 2000)

    It comes back as a syntax error. I have attached a picture of the error message.
    Attached Images Attached Images

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Figuring Current School Year (Access 2000)

    Your browser mangled the SQL string - this is not your fault, it just happens. Replace the two occurrences of & l t ; (without the spaces) by the < character. See screenshot.
    Attached Images Attached Images
    • File Type: png x.PNG (4.3 KB, 0 views)

  9. #9
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Figuring Current School Year (Access 2000)

    Thank you so much!!! I really appriciate your time and quickness to help me!!!

Posting Permissions

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