Solved

How to determine age over 18 in WHERE statement

Posted on 2013-10-25
9
241 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

791 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