Solved

How to determine age over 18 in WHERE statement

Posted on 2013-10-25
9
238 Views
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.

Suggestions?

Thanks
0
Comment
Question by:Joppa
9 Comments
 
LVL 12

Expert Comment

by:pdebaets
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.
0
 

Author Comment

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

Expert Comment

by:pdebaets
ID: 39602245
What is the error message?
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 39602272
Hi,

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


Cheers,

Christian
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

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]"

/gustav
0
 

Author Closing Comment

by:Joppa
ID: 39602686
Thanks
0
 

Author Comment

by:Joppa
ID: 39602688
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
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
0
 

Author Comment

by:Joppa
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).
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now