convert birthdate to age

Posted on 2008-11-11
Last Modified: 2013-11-28
I have two fields in my access form  AgeG and Agel that I need added to the query below. I need to find the age of the person from the dob field then get all records that fall in that age group between ageG and AgeL.  What is the best way to handle this?
SELECT tblDemo.clinic_code, tblDemo.chart_number, tblDemo.last_name AS LastName, tblDemo.first_name AS Firstname, tblDemo.address1 AS Address1, tblDemo.address2 AS Address2, AS City, tblDemo.state AS State, AS Zip, tblDemo.phone1 AS Phone1, tblDemo.phone2 AS Phone2,, tblDemo.dob

FROM tblDemo LEFT JOIN Qry_ListPatientReceiveService2 ON (tblDemo.chart_number = Qry_ListPatientReceiveService2.chart_number) AND (tblDemo.clinic_code = Qry_ListPatientReceiveService2.clinic_code)

WHERE (((Qry_ListPatientReceiveService2.chart_number) Is Null) AND ((Activeclinic2([tblDemo]![clinic_code]))=True) AND ((Activeperiod2([tblDemo].[start_date],Nz([tblDemo].[end_date],Date())))=True)) AND (([Forms]![frmMain2]![txttemp]);

Open in new window

Question by:cseink
    1 Comment
    LVL 17

    Accepted Solution

    You can use the DateDiff function:
    When you do your select it will look something like this.
    WHERE DateDiff ( y, DateOfBirth, NOW()) BETWEEN AgeG AND AgeI

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    745 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

    17 Experts available now in Live!

    Get 1:1 Help Now