Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to determine age over 18 in WHERE statement

Posted on 2013-10-25
9
Medium Priority
?
250 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 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 52

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 49

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

604 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