Need help with query with 2 different dates

[sb2diver]

I volunteered (first mistake) to do a data base for my kids Little League. Everything is going well until I get to the League Age which is different from actual age. 04/30/2009 is the cut off date.

John dob is 04/30/1997 he's age 12 and his league age would be 12. If
johns dob is 05/01/1997 he's age is 12 but his league age would be 11.


l have searched everywhere and tried a lot of query strings but this one has got me baffled.
Can anyone steer me in the right direction?

 

[MagicMan]

Try Dob < DateAdd("y", -12, #04/30/2009#) in your criteria.
Smiles
Bob
DateAdd description:
y=years, -12 is take a way 12 ... translates to take away 12 years from 04/30/2009

Hope that helps and welcome to the forums.

 

[raskew]

First problem:

If John's DOB is #4/30/97# or #5/1/97# his real age is 11, not 12. He won't be 12 until 2009.

Once that's sorted out, what's the problem?

Bob

Added:

MagicMan: You need to check the help file and test your solutions.. "y" as used in the
DateAdd() function = "Day of Year", so
? DateAdd("y", -12, #04/30/2009#) = 4/18/2009

Try "yyyy"

 

[MagicMan]

Sorry, thanks for the correction, flew this out just before bed off the top of my head.

 

[sb2diver]

Thank you for responding to my question.

I apologize that my example was not very clear.

I have a list of 450 kids in the Little League from 4 years old to 14 years old. I need to figure there league age for all the kids. The league age cut off date is 4/30/2009.

If John dob is 04/30/1997 he's age 12 and his league age would be 12.
If johns dob is 05/01/1997 he's age is 12 but his league age would be 11.

Below is the attached chart we have to use to figure the league age of each child. Note this chart changes every year. This is a 2008 chart.

View attachment 2008 Age Determination Chart.doc

Hope this helps. Because I sure can’t figure this one.

 

[MagicMan]

Try :

Dob < DateAdd("yyyy", -12, #04/30/2009#) in your criteria.

Corrected thanks to raskew.

 

[sb2diver]

Thank You

Will give that a try.