INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Return a value based off childs DOB falling between 2 dates

Return a value based off childs DOB falling between 2 dates

(OP)
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:

CODE --> datasheet

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 --> datasheet

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

RE: Return a value based off childs DOB falling between 2 dates

From your post:

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

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

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.

RE: Return a value based off childs DOB falling between 2 dates

(OP)
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

CODE --> datasheet

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 

RE: Return a value based off childs DOB falling between 2 dates

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 3 months each.

So if (below) is a table of your terms, you can simply ask:

Select Term
From MyTermTable
Where (Child's_DOB + 3 years + 3 months) Between From and To


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
Sum-16		01/04/2016	31/08/2016
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 
 

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close