determine a formula that will let me

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

debbiep@ywcaofjamestown.com - 09 Jul 2007 01:02 GMT

I am looking for help to determine a formula that will let me
determine which children who will be 5 years old by Dec 1, 2007. in a
query

Col
A [FirstName]
B [LastName]
C [DOB]
D [Classroom] (Kool Kidz, Kinder-Ready
If they are 5 in Dec, their classroom will default to KOOL KIDZ;
otherwise, they will be KINDER-READY
Can any one help me with this formula.

Reply to this Message

John W. Vinson - 09 Jul 2007 01:23 GMT

>I am looking for help to determine a formula that will let me
>determine which children who will be 5 years old by Dec 1, 2007. in a
[quoted text clipped - 8 lines]
>otherwise, they will be KINDER-READY
>Can any one help me with this formula.

IIF(DateDiff("yyyy", [DOB], DateSerial(Year(Date()), 12, 1)) < 5,
"KINDER-READY", "KOOL KIDZ")

as a calculated field in a query should do the trick for you.

<soapbox>I dislike the overly cutesy misspelling. You're teaching children to
read and write - and teaching them to read and write INCORRECTLY. Not, IMO,
what schools should do. </soapbox>

John W. Vinson [MVP]

Reply to this Message

louisjohnphillips@gmail.com - 09 Jul 2007 01:28 GMT

On Jul 8, 5:23 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:

> On Sun, 08 Jul 2007 17:02:26 -0700, "debb...@ywcaofjamestown.com"
>
[quoted text clipped - 22 lines]
>
> John W. Vinson [MVP]

Assume a 5-year-old is between 1825 and 2195 days old.

So

select FirstName, LastName,
iif( CDate( #12/01/2007# ) - DOB > 1825 and CDate( #12/01/2007# ) -
DOB < 2195, 'KOOL KIDZ', 'KINDER-READY' ) as Classroom
from tblTable

Can this knowledge be used to set a default value for the Classroom
column?

Reply to this Message