Return a value based off childs DOB falling between 2 dates

[spartansFC]

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:

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:

test: IIf([dteDOB]>=[dteTermFromDate] And [dteDOB]<=[dteTermToDate],1,0)

which produced these results

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

 

[Andrzejek]

From your post:

A child at a nursery will receive funding the term after they turn 3 ([blue]days? weeks? month? years?[/blue]) so for example if a childs's date of birth is:

29.01.2016, funding starts 01.04.2016 [blue]a little over 2 months later[/blue]
31.03.2016, funding starts 01.04.2016 [blue]the very next day[/blue]
01.04.2016, funding starts 01.09.2016 [blue]6 months later[/blue]

The test data i used was DOB: 29.01.13 which means the query should return a 1 next to 01.04.2016

Where is this "1 next to 01.04.2016"?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[spartansFC]

okay, so the terms are

Summer - 01.04 to 31.08
Autumn - 01.09 to 31.12
Spring - 01.01 to 31.03

So in the test data of 29.01.13, the child turned 3 in Spring so would receive funding in the summer term after.

in the output, there is a 1 next to 01.04.16 but there's also a 1 next to 01.05.15, 01/04/2015, 01/09/2015 etc

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

 

[Andrzejek]

Let me get another crack at it.

So if the child was born on 29/01/2013, the child turned 3 years old on 29/01/2016 (in Spring term, but who cares). So the child would receive funding in the (next term) summer term after.

Your 'terms' last [green]3 months[/green] each.

So if (below) is a table of your terms, you can simply ask:
[tt]
Select Term
From MyTermTable
Where (Child's_DOB + 3 years + [green]3 months[/green]) Between From and To
[/tt]

[pre]
Term From To
Primary 01/04/2010 31/12/2010
PrePrim 01/01/2011 31/08/2011
Aut-11 01/09/2011 31/12/2011
Spr-12 01/01/2012 31/03/2012
Sum-12 01/04/2012 31/08/2012
Aut-12 01/09/2012 31/12/2012
Spr-13 01/01/2013 31/03/2013
Sum-13 01/04/2013 31/08/2013
Aut-13 01/09/2013 31/12/2013
Spr-14 01/01/2014 31/03/2014
Sum-14 01/04/2014 31/08/2014
Aut-14 01/09/2014 31/12/2014
Spr-15 01/01/2015 31/03/2015
Sum-15 01/04/2015 31/08/2015
Aut-15 01/09/2015 31/12/2015
Spr-16 01/01/2016 31/03/2016[blue]
Sum-16 01/04/2016 31/08/2016[/blue]
Aut-16 01/09/2016 31/12/2016
Spr-17 01/01/2017 31/03/2017
Sum-17 01/04/2017 31/08/2017
Aut-17 01/09/2017 31/12/2017
[/pre]

Am I any closer to what you are after?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.