query birthdates occuring in year range

  • Thread starter Thread starter Mary
  • Start date Start date
M

Mary

The following is my sql view of a query I have put together to return the
year of a member's 100th birthday. My assignment is to generate a report for
those who are turning 100 between and including 2009 to 2013. I am able to
get as far as calculating the year each member turns 100, but am having
difficulty in limiting the results to 2009-2013. Your help is very much
appreciated.

SELECT tblCustomerAddresses.IndexName, tblCustomerAddresses.CustomerType,
tblPermanentRecordInformation.BirthDate,
DateSerial(Year([BirthDate])+100,Month([BirthDate]),Day([BirthDate])) AS
100thBirthday, DatePart("yyyy",[100thBirthday]) AS 100thYear
FROM tblPermanentRecordInformation INNER JOIN tblCustomerAddresses ON
tblPermanentRecordInformation.[ID Number]=tblCustomerAddresses.[ID Number]
WHERE (((tblCustomerAddresses.CustomerType)="Sister"))
ORDER BY
DateSerial(Year([BirthDate])+100,Month([BirthDate]),Day([BirthDate]));
 
one approach would be to put this criteria in the query's birthdate field:
=1/1/1909 AND <1/1/1914
Click to expand...

this limits the results to those who qualify....

and if you need just their year alone in its own field, then create a new
calculated field based upon birthdate:

100Year: Year([NameOfBirthdateField])

--
NTC


Mary said:
The following is my sql view of a query I have put together to return the
year of a member's 100th birthday. My assignment is to generate a report for
those who are turning 100 between and including 2009 to 2013. I am able to
get as far as calculating the year each member turns 100, but am having
difficulty in limiting the results to 2009-2013. Your help is very much
appreciated.

SELECT tblCustomerAddresses.IndexName, tblCustomerAddresses.CustomerType,
tblPermanentRecordInformation.BirthDate,
DateSerial(Year([BirthDate])+100,Month([BirthDate]),Day([BirthDate])) AS
100thBirthday, DatePart("yyyy",[100thBirthday]) AS 100thYear
FROM tblPermanentRecordInformation INNER JOIN tblCustomerAddresses ON
tblPermanentRecordInformation.[ID Number]=tblCustomerAddresses.[ID Number]
WHERE (((tblCustomerAddresses.CustomerType)="Sister"))
ORDER BY
DateSerial(Year([BirthDate])+100,Month([BirthDate]),Day([BirthDate]));
Click to expand...
 
Thank you so much!
I got so caught up in the calculations that it didn't occur to me to limit
the results based on (this year - 100 years). What an easy fix.
Great thinking and thanks again.


NetworkTrade said:
one approach would be to put this criteria in the query's birthdate field:
=1/1/1909 AND <1/1/1914
Click to expand...

this limits the results to those who qualify....

and if you need just their year alone in its own field, then create a new
calculated field based upon birthdate:

100Year: Year([NameOfBirthdateField])

--
NTC


Mary said:
The following is my sql view of a query I have put together to return the
year of a member's 100th birthday. My assignment is to generate a report for
those who are turning 100 between and including 2009 to 2013. I am able to
get as far as calculating the year each member turns 100, but am having
difficulty in limiting the results to 2009-2013. Your help is very much
appreciated.

SELECT tblCustomerAddresses.IndexName, tblCustomerAddresses.CustomerType,
tblPermanentRecordInformation.BirthDate,
DateSerial(Year([BirthDate])+100,Month([BirthDate]),Day([BirthDate])) AS
100thBirthday, DatePart("yyyy",[100thBirthday]) AS 100thYear
FROM tblPermanentRecordInformation INNER JOIN tblCustomerAddresses ON
tblPermanentRecordInformation.[ID Number]=tblCustomerAddresses.[ID Number]
WHERE (((tblCustomerAddresses.CustomerType)="Sister"))
ORDER BY
DateSerial(Year([BirthDate])+100,Month([BirthDate]),Day([BirthDate]));
Click to expand...
Click to expand...
 
Back
Top