How to determine age over 18 in WHERE statement

Posted on 2013-10-25
Medium Priority
Last Modified: 2013-10-26
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.


Question by:Joppa
LVL 12

Expert Comment

ID: 39601825
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.

Author Comment

ID: 39602097
Same issue.  I did try changing the query names too but it made no difference.
LVL 12

Expert Comment

ID: 39602245
What is the error message?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 24

Accepted Solution

Bitsqueezer earned 2000 total points
ID: 39602272

you missed some spaces in the string, and square brackets around field names with spaces or special characters (you should also not use field names which are keywords or function names like "First" and "Last").

Try it with this one:

strSQL = "SELECT [First], [Last], Street, City, State, Zip, [Phone #] " & _
         "  FROM Table1 " & _
         " WHERE Junior = Yes AND ((" & (Format(Now(), "yyyy") + 1) & " - Year(Birthdate)) > 18) " & _
         " ORDER BY [Last]"

Open in new window


LVL 53

Expert Comment

by:Gustav Brock
ID: 39602480
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]"


Author Closing Comment

ID: 39602686

Author Comment

ID: 39602688

  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.

LVL 49

Expert Comment

ID: 39603341
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

Author Comment

ID: 39603394
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).

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
This tutorial summarizes the causes behind"an unknown error prevented access to the PST File”.  It also suggests the various solutions to fix the problem.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question