Solved

How to determine age over 18 in WHERE statement

Posted on 2013-10-25
9
243 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 50

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

737 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