Start my year in September

[Techie2k]

Hi.

I work in education and I'm currently creating a database for all our SEN pupils. Because their year group will increase by 1 each September I use 'Start Date' and work out the year group from that. That way it will increment automatically each year.

Problem is, it won't update till January, and it needs to +1 on September 1st.

At the moment I use a calculated field in my table with the Expression

((2014-[StartDate])+6)

How do I add to that expression that if the current year is after September then we add 1 to the year group.

e.g.

I'm adding in our current Year 7's at the moment.

• I put Start Date into my form as 2013 (it's a value list btw) and the Year Group shows 7.

Which at the moment is correct, but come September, it needs to change to 8.

How do I expand my expression to reflect the above?

Cheers all

T.

I've also just noticed that I'm using 2014 as a constant.. How would I change that to run from 'now'

 

[namliam]

Well you should start at the core of this problem, being you are not storing your startdate as a proper date field.
If you were and had Sept 1, 2009 in there a simple date diff would calculate it for you

 

[Techie2k]

Cheers for your reply - thats that thing i need help with. How do i do what i wnat to accomplish?

Thanks

Chris

 

[burrina]

This will give you the current September of the current year.

Sep: DateSerial(Year(Date()),1,9)

Jan: DateSerial(Year(Date()),1,1)

CurMonth: Format(DateSerial(Year(Date()),Month(Date()),Day(Date())),"mmmm ")

HTH

 

[Techie2k]

Hi guys.

Thanks for the help so far.. It's taking a bit to sink in though sorry

Should my expression now look like this?

(((DateSerial(Year(Date()),1,9))-[StartDate])+6

-----------------------------

I tried that, it said no..

So.. I've made another table and called it tblCurrentYear. in there is one date field with 1/09/2014 in it called dteCurrentYear. I've no problems with updating this field once a year if necessary.
How do I reference that field in my expression from my main database?

 

[burrina]

You can use a Lookup.

Me.SomeField = DLookUp("[dteCurrentYear]","tblCurrentYear")

HTH

 

[namliam]

If you want to calculate the "most recent" Sept 1...

dateadd("M",-9,Date()) will force the current date back 9 months, resulting in the year where you need to be using the year() function.

Year(dateadd("M",-9,Date()) )

Then if you need the actual sept 1st date of that year:
dateserial(Year(dateadd("M",-9,Date()) ),9,1)

Is that what you want/need?

 

[Techie2k]

Hi all..

Thanks for the suggestions but I'm not having much success..

I've attached a screen shot of the table and the form - maybe that will help.

Cheers again.



ps - nbrYearGroup is the box with a number 7 in it.. our tutor groups are 7X, 7Y, 7Z, 8Y etc etc

 

[pr2-eugin]

If you'd have mentioned that you are using Calculated field I/other posted would have said to you, why your design is not idea. Since you have brought it to the light now, here you go.

Calculated Fields: http://allenbrowne.com/casu-14.html

 

[Techie2k]

I did though?

At the moment I use a calculated field in my table with the Expression

((2014-[StartDate])+6)

 

[burrina]

Try this: This was on a form. do you need it in a qry?

=DateSerial(Year(Date()),1,1)+8

HTH

 

[plog]

The best way to do this is with a function in a module:

Public Function get_SchoolYear(in_Date) As Integer
    ' takes a date (in_Date) and returns the school year it falls in

ret=Year(in_Date)         ' return value, default is calendar year of in_Date

If (Month(in_Date)>=9) Then ret=ret+1
    ' if in_Date is September or later, moves year to next year

get_SchoolYear=ret

End Function

Once you have that function in a module, you can use it anywhere, on any date. For example, you could create a query to determine what school year a every pupil started:

SELECT PupilID, PupilFirstName, PupilLastName, get_SchoolYear(StartDate) as YearStart
FROM PupilTable;

You could also use it on a form/report, to put the current date's school year atop it:

=get_SchoolYear(Date())

A function is probably the best way to handle this.

 

[vbaInet]

To be honest, if I were you I would go through a roll over process, i.e. run a query to migrate the kids from their current year group to the next year group. This is what normally happens on the school's database anyway.

You could ask your IT department to give you a fresh list every September and run some queries to update your database.

 

[Techie2k]

Many thanks for all your help..

In the end I went for the public function - mainly because I understood that one..

I can see the simplicity in the roll over process, but there is still a fair bit of roll over stuff to do each year. I wanted to automate as much as possible really.

Sorry if I've been a bit vague in my explanations.

T.

 

[vbaInet]

Well the reason why I brought it up is because of this... in the very unlikely event that a child fails to move up a level, what do you do? As you know, there's a very slim chance but it happens.

 

[Techie2k]

Well the reason why I brought it up is because of this... in the very unlikely event that a child fails to move up a level, what do you do? As you know, there's a very slim chance but it happens.

Hi vbaInet,

Reason being is I'm making this database for use in school and not everyone knows how to run queries, excel flummoxes most people. I'm making a system that run's itself as much as possible to limit end user 'messing'.. I won't be in this job forever so I don't want to leave the school with a system they don't know how to use.

If I knew that this was going to be run by someone with technical knowledge then yes, I could include a roll over process - but to me it's a button press that doesn't need to be pressed..

(hope I don't come across arrogant or anything, appreciate your advice and may include something like in other parts of the database. You've already planted seed of thought re. an admin panel..... )

 

[vbaInet]

Not a problem. I've done some work in the Education sector before and built some aspects of it so I was just giving you some insight.

Your school's pupil management system will mostly have a SEN tickbox that identifies those pupils which special needs and if the data is managed properly your IT department can easily build a quick query to pull this data and the necessary fields required. I would be inclined to get this data directly from them.

As with most schools a pupil doesn't necessarily move into the same form group (e.g. 9A to 10A) and in this case if you require form groups in your system you will need to manually update this info. This is another scenario you need to consider.

The seed of thought I'm trying to plant is not really an admin panel but requesting the data directly from your IT department at the end of year (as they tend to run the end of year process weeks before the start of the new academic year).

Good luck though

 

[Techie2k]

Not a problem. I've done some work in the Education sector before and built some aspects of it so I was just giving you some insight.

Your school's pupil management system will mostly have a SEN tickbox that identifies those pupils which special needs and if the data is managed properly your IT department can easily build a quick query to pull this data and the necessary fields required. I would be inclined to get this data directly from them.

As with most schools a pupil doesn't necessarily move into the same form group (e.g. 9A to 10A) and in this case if you require form groups in your system you will need to manually update this info. This is another scenario you need to consider.

The seed of thought I'm trying to plant is not really an admin panel but requesting the data directly from your IT department at the end of year (as they tend to run the end of year process weeks before the start of the new academic year).

Good luck though

I am the guy that sorts the data out at the end of the year.. and yes our 'whole school system' should monitor our SEN kids - but it doesn't. I'm therefore trying to create our own version specific to our monitoring and tracking needs.

If you've been in education then you'll be aware that the SEN department can become quite large. This database and future expansions of it should be in the main school system (and I've tried asking) - if I don't do it myself, then it'll never happen, we'll get too big for our current system and things will start going wrong.

argh.. I always worked in what I call 'proper jobs' before I came to a school - and boy do they take their time here lol.

 

[vbaInet]

Yep, a data cleansing exercise can take ages but it's definitely worth it.

Sounds like you're a one man team. Good luck there

 

[Techie2k]

Not quite one man.. Though it is a lot to get my head round.. Really wish sometimes I could grab a VBA expert and sit them next to me for a day lol.