determine a formula that will let me

[debbiep]

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.

 

[John W. Vinson]

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.

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]

 

[louisjohnphillips]

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.

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]

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?