spartansFC
Programmer
Hi
I've managed to do this problem in excel but i need to convert it onto access. The problem is i need to return a 1 when a child hits a certain date grouping.
A child at a nursery will receive funding the term after they turn 3 so for example if a childs's date of birth is:
29.01.2016, funding starts 01.04.2016
31.03.2016, funding starts 01.04.2016
01.04.2016, funding starts 01.09.2016
the term dates grouping table looks like:
The test data i used was DOB: 29.01.13 which means the query should return a 1 next to 01.04.2016
i tried the IIF statement in the query:
which produced these results
It is trying to do what i want it to do, anyone have any ideas
Michael
I've managed to do this problem in excel but i need to convert it onto access. The problem is i need to return a 1 when a child hits a certain date grouping.
A child at a nursery will receive funding the term after they turn 3 so for example if a childs's date of birth is:
29.01.2016, funding starts 01.04.2016
31.03.2016, funding starts 01.04.2016
01.04.2016, funding starts 01.09.2016
the term dates grouping table looks like:
Code:
Term From To
Primary 01/04/2010 to 31/12/2010
PrePrim 01/01/2011 to 31/08/2011
Aut-11 01/09/2011 to 31/12/2011
Spr-12 01/01/2012 to 31/03/2012
Sum-12 01/04/2012 to 31/08/2012
Aut-12 01/09/2012 to 31/12/2012
Spr-13 01/01/2013 to 31/03/2013
Sum-13 01/04/2013 to 31/08/2013
Aut-13 01/09/2013 to 31/12/2013
Spr-14 01/01/2014 to 31/03/2014
Sum-14 01/04/2014 to 31/08/2014
Aut-14 01/09/2014 to 31/12/2014
Spr-15 01/01/2015 to 31/03/2015
Sum-15 01/04/2015 to 31/08/2015
Aut-15 01/09/2015 to 31/12/2015
Spr-16 01/01/2016 to 31/03/2016
Sum-16 01/04/2016 to 31/08/2016
Aut-16 01/09/2016 to 31/12/2016
Spr-17 01/01/2017 to 31/03/2017
Sum-17 01/04/2017 to 31/08/2017
Aut-17 01/09/2017 to 31/12/2017
The test data i used was DOB: 29.01.13 which means the query should return a 1 next to 01.04.2016
i tried the IIF statement in the query:
Code:
test: IIf([dteDOB]>=[dteTermFromDate] And [dteDOB]<=[dteTermToDate],1,0)
which produced these results
Code:
test dte3AND4Start
0 01/01/2015
0 01/04/2015
0 01/09/2015
0 01/01/2016
0 01/04/2016
0 01/09/2016
0 01/01/2017
1 01/01/2015
1 01/04/2015
1 01/09/2015
1 01/01/2016
1 01/04/2016
1 01/09/2016
1 01/01/2017
It is trying to do what i want it to do, anyone have any ideas
Michael