if formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a database for a college. I've recorded all the applicants and
recorded who was accepted and who was refused for 2 years. I now want to
create a report of who is in year 1 and who is in year 2. The date i'll use
to work this out is Student Start Date (i.e. the date they will begin or
began college.)
But the problem is they begin say 1-sep-05 so how can i say that if the
month now when i'm running the report is between sep and Jan then if the
start year is same as year of today then they are year 1 and if the months
are between jan and august then if the start year is same as year of today
minus 1 then they are year 1 and then the same thing with year 2 just
minusing first 1 and then 2
Do you get it?!!
Thanks for help!
 
Are all the begin dates Sept 1st? Do any students begin in different
semesters? Do you want your report to always be relative to the current
school year?

It would really help if you would take the time to type about 10 records
into a message and include the value that should be calculated.
 
Yes it does always go by the school year but they don't always begin on the
1st of September sometimes earlier and sometimes bit later does that matter?

So at any point during the school year i want to run a report and see the
list of the 2 years. So Student Start Date will be for e.g 1-Sept-05 and for
year 2 it could be 5-Sept-05 but each year of girls will all have the same
start date, there's no different semesters.
 
Could you provide the 10 records so we have an accurate picture of your data
and desired results?
 
Hi thanks. I'm not sure which fields you want because the student table is
quite big. Basically it has

Student Application Country Town Address Surname Name
Start Date Date
1-Sep-05 1-Jan-05 England Newcastle 5 wild rd Simon Debrah
1-Sep-05 13-Feb-05 USA 2 wot rd Blue Sara
5-Sep-04 14-Jan-04 France 3 any st Black Jessica
5-Sep-04 17-Mar-04 Belguim 5 tick lane Brown Heather
1-Sep-05 7-Mar-05 Canada 4 fig rd White Lee
5-Sep-04 5-Feb-04 Russia 5 joy st Hariss Pearl
30-Aug-03 8-Jan-03 Australia 10 le rd Russell Susan
30-Aug-03 1-Apr-03 Belguim 44 fe st Bork Sandra
2-Sep-05 4-Apr-02 France 55 gap rd Flax Margaret
2-Sep-05 5-Feb-02 USA 6 cl st Wullik Joyce


This is the general idea whoever applies for the beginning of the school
year will all begin on the same day i.e the day the school year starts , the
problem is that it doesn't always start on the 1st September. So basicall
with all this info that the college has for the last 10 years or so i want to
run forms and reports of only the current students. So it's a 2 year college
so basicaly anyone who began in '03 and '04 and i want to be able to make
lists etc. for the coming year i.e. anyone who began/will begin in '04 and
'05 GET IT??!!
 
I was expecting you to reply with StudentStartDates and your expected result
of the calculation. For instance

[Student Start Date] [YearInSchool]
1-Sep-05 1
4-Sep-04 2
1-Oct-03 3
30-Sep-05 1

Have you looked at using the DateDiff() function? You might want to try:
DateDiff("yyyy",[Student Start Date],Date())
 
Thank you i think i'm getting there. The thing is that it doesn't work with
the year because they could still be in the first year but if it's after
January then the formula will give it a number 2! So i did the date diff
formula as you wrote but like this

Expr1: DateDiff("d",[Date of Action],Date())

I then get a number of days eg. 300, 550 etc. So now i want to view the 2
current classes in the college.
So i need help with the if formula. I want to say that if Expr1 is >0 and
<365 it should be called Class 1 and if its >365 and <730 it should be called
A2!
Thanks so much for all you help!!
Duane Hookom said:
I was expecting you to reply with StudentStartDates and your expected result
of the calculation. For instance

[Student Start Date] [YearInSchool]
1-Sep-05 1
4-Sep-04 2
1-Oct-03 3
30-Sep-05 1

Have you looked at using the DateDiff() function? You might want to try:
DateDiff("yyyy",[Student Start Date],Date())

--
Duane Hookom
MS Access MVP
--

Database User said:
Hi thanks. I'm not sure which fields you want because the student table is
quite big. Basically it has

Student Application Country Town Address Surname Name
Start Date Date
1-Sep-05 1-Jan-05 England Newcastle 5 wild rd Simon Debrah
1-Sep-05 13-Feb-05 USA 2 wot rd Blue Sara
5-Sep-04 14-Jan-04 France 3 any st Black Jessica
5-Sep-04 17-Mar-04 Belguim 5 tick lane Brown Heather
1-Sep-05 7-Mar-05 Canada 4 fig rd White Lee
5-Sep-04 5-Feb-04 Russia 5 joy st Hariss Pearl
30-Aug-03 8-Jan-03 Australia 10 le rd Russell Susan
30-Aug-03 1-Apr-03 Belguim 44 fe st Bork Sandra
2-Sep-05 4-Apr-02 France 55 gap rd Flax Margaret
2-Sep-05 5-Feb-02 USA 6 cl st Wullik Joyce


This is the general idea whoever applies for the beginning of the school
year will all begin on the same day i.e the day the school year starts ,
the
problem is that it doesn't always start on the 1st September. So basicall
with all this info that the college has for the last 10 years or so i want
to
run forms and reports of only the current students. So it's a 2 year
college
so basicaly anyone who began in '03 and '04 and i want to be able to make
lists etc. for the coming year i.e. anyone who began/will begin in '04 and
'05 GET IT??!!
Click to expand...
Click to expand...
 
Back
Top