asked on

How to determine age over 18 in WHERE statement

I'm trying to select all juniors which are defined as under 18 that are in the database that will be over 18 next year. This is what I have but it errors out:

' 21 Display Juniors Over 18
Case JrOver18

strSQL = "SELECT Table1.First, Table1.Last, Table1.Street, Table1.City, Table1.State, " & _
"Table1.Zip, Table1.Phone #" & _
"FROM Table1 " & _
"WHERE " & "Table1.Junior = Yes AND" & (Format(Now(), "yy") + 1) - Format(Table1.Birthdate, "yy") > 18 & _
"ORDER BY Table1.Last"
CurrentDb.QueryDefs("Membership").SQL = strSQL
DoCmd.OpenQuery "jr age"

strSQL is string dim at the top of the function.

Suggestions?

Thanks

pdebaets

Seems like you're opening the query named "jr age". You may want to try opening the query named "Membership".

Also, try this for your Where clause line:

"WHERE " & "Table1.Junior = Yes AND" & ( (cint(Format(Now(), "yyyy")) + 1) - cint(Format(Table1.Birthdate, "yyyy")) ) & " > 18 " & _

If that doesn't work, please post back here.

Joppa

ASKER

Same issue. I did try changing the query names too but it made no difference.

pdebaets

What is the error message?

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

If they will be over 18 next year, they will be 18 this year.
Also, why not use the direct function Year:

strSQL = "SELECT [First], [Last], Street, City, State, Zip, [Phone #] " & _
" FROM Table1 " & _
" WHERE Junior = True AND (Year(Date() - Year(Birthdate) = 18) " & _
" ORDER BY [Last], [First]"

/gustav

Joppa

ASKER

Thanks

Joppa

ASKER

Gustav,

You are correct and pointed out an error in my thinking. For this I needed to list everyone in the table that was 18 and older to remove them the first time and clean up the table. When I read you comment I realized that I needed to check for >= to get it right.

Thanks.

PortletPaul

just as an observation an accurate calculation of age isn't purely based on comparison of year

as I write this in October 2013, a baby born last November or December isn't yet 1 year old, but the comparison of this_year - year_of_birth (2013 - 2012) = 1

If the day & month of the date-of-birth is later than the the day & month of the evaluation point then you need to deduct 1

Joppa

ASKER

That is also correct, but in this instance this is to determine the club dues for the member. The year's dues are base on being a junior( less than 18 in that year) or a full member(18 or older in that year).