Specific Age Query for a Date Range

Table = Personal
Field = Birthdate

I need to create a query to show who is of a certain age for January 1, 2008 
to December 31, 2008.

For example, I need to know who will be 50, 60, 65, 70 and 75.

I would like to create the query so that when I click the query to open it, 
I have to input the age.

I've done other queries to show who is of a specific age as of today:  
AgeYears: 
DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), 
with criteria of [Age in Years] ... but I cannot figure out how to pull a 
report saying who will be 50 this year, or 60 or 65, for the entire year 
beginning January 1.

Can anyone please point me in the right direction?
0
Utf
1/18/2008 7:32:02 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1633 Views

Similar Articles

[PageSpeed] 11

I'm not sure your question makes sense. Unless the person's birthday is 
January 1st, they're not going to have the same age for the entire year!

If what you're trying to do is get a list of everyone who'll be, say, 50 
during the year, you can simply look at the year of birth. If it's 50 less 
than the current year, you've found them.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"B. Levien" <BLevien@discussions.microsoft.com> wrote in message 
news:94DC1B7F-9EA7-4565-8DE7-38F227134A85@microsoft.com...
> Table = Personal
> Field = Birthdate
>
> I need to create a query to show who is of a certain age for January 1, 
> 2008
> to December 31, 2008.
>
> For example, I need to know who will be 50, 60, 65, 70 and 75.
>
> I would like to create the query so that when I click the query to open 
> it,
> I have to input the age.
>
> I've done other queries to show who is of a specific age as of today:
> AgeYears:
> DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0),
> with criteria of [Age in Years] ... but I cannot figure out how to pull a
> report saying who will be 50 this year, or 60 or 65, for the entire year
> beginning January 1.
>
> Can anyone please point me in the right direction? 


0
Douglas
1/18/2008 7:43:47 PM
If they will turn a specific age this year, you don't need to calculate the 
month.  The following expression will return the number of years between the 
birthday year and the current year.  The person will turn that age sometime 
during the year. The filter for the year values you want:

Calculate
AgeYears: Year(Date) - Year([Birthdate])

Filter for years
Where Year(Date) - Year([Birthdate]) IN(50, 60, 65, 70, 75)
-- 
Dave Hargis, Microsoft Access MVP


"B. Levien" wrote:

> Table = Personal
> Field = Birthdate
> 
> I need to create a query to show who is of a certain age for January 1, 2008 
> to December 31, 2008.
> 
> For example, I need to know who will be 50, 60, 65, 70 and 75.
> 
> I would like to create the query so that when I click the query to open it, 
> I have to input the age.
> 
> I've done other queries to show who is of a specific age as of today:  
> AgeYears: 
> DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), 
> with criteria of [Age in Years] ... but I cannot figure out how to pull a 
> report saying who will be 50 this year, or 60 or 65, for the entire year 
> beginning January 1.
> 
> Can anyone please point me in the right direction?
0
Utf
1/18/2008 8:13:00 PM
Klatuu, that is EXACTLY what I was looking for!!!!  Thank you very much!!!!

"Klatuu" wrote:

> If they will turn a specific age this year, you don't need to calculate the 
> month.  The following expression will return the number of years between the 
> birthday year and the current year.  The person will turn that age sometime 
> during the year. The filter for the year values you want:
> 
> Calculate
> AgeYears: Year(Date) - Year([Birthdate])
> 
> Filter for years
> Where Year(Date) - Year([Birthdate]) IN(50, 60, 65, 70, 75)
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "B. Levien" wrote:
> 
> > Table = Personal
> > Field = Birthdate
> > 
> > I need to create a query to show who is of a certain age for January 1, 2008 
> > to December 31, 2008.
> > 
> > For example, I need to know who will be 50, 60, 65, 70 and 75.
> > 
> > I would like to create the query so that when I click the query to open it, 
> > I have to input the age.
> > 
> > I've done other queries to show who is of a specific age as of today:  
> > AgeYears: 
> > DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), 
> > with criteria of [Age in Years] ... but I cannot figure out how to pull a 
> > report saying who will be 50 this year, or 60 or 65, for the entire year 
> > beginning January 1.
> > 
> > Can anyone please point me in the right direction?
0
Utf
1/21/2008 4:05:02 PM
Reply:

Similar Artilces:

Problem with Date Manipulation
Okay.. Im not a kid, 36, been working with excel for years & last year did up to intermediate courses in Access. However, that being said because you guys are very keen in here, so I dont want to sound stupid, whilst my understanding on alot of excel is generally on the layman terms! Heres my problem. Im making a simple spreadsheet. I have a widget that enters my facility on one date, it then exits another. I know by setting up EXIT - ENTER = DAYS I have my duration in the facility. BUT---> I dont want the weekends to count as days! Material arrives every weekday & exits the...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

Calculating totals by month from whole date
I'm trying to figure out the proper calculation to pull totals by dat in Excel. Here's my problem: I have a workbook with 2 worksheets; one is the main data, the other i the statistics from the main data. Within the main data, there is column titled "date paid" and another column titled "total paid" (ther are a bunch more columns, but they don't matter for this problem). Th "date paid" column will contain dates such as 1/1/04, 5/15/04 etc. O the stats page, I have columns titled for each month of the year an would like each to include how much was pa...

Export a range to a text file
Hello need some advise on how to procede I need to be able to create a text file containg some text as well as data that is within a named range in excel and then some more text. I can handle printing to the text files using cell values etc but am unsure of the best way to print the ranges data. Is there a way or procedure to just print the range as is in csv format? As well my range will contain about 6 columns, each containg a number field (formatting of decimal places is important, some have 2 dec some 3 etc) Also the range has a max of 50 rows however will always contain lower rows of...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

Calling VBA subroutine from a query?
Can I call a VBA subroutine from within an Access query? I wrote some English to Metric conversion routines in the Access VBA code and would like to run a query on the data that will return coverted values. I need to be able to execute this from outside the database (run the query from another program). Yes. The function must be a Public Function in a standard module. You use a calculated control to return the results of the query. ConvertedValue: MyFunction([SomeField]) -- Dave Hargis, Microsoft Access MVP "DavidY" wrote: > Can I call a VBA subroutine from within an ...

converting dates
Hi All I have a couple of excel problems to do with dates (Excel XP/2002). I have a formula that displays one date minus another and displays the answer as the number of days. Firstly, is it possible to convert the data to display as year and days (375 displays as 1 year and 10 days) as it displays as a decimal point when I divide by 365. Secondly does anyone know a formula that sorts out leap years (see above). Any help, suggestions or tutorial links greatly appreciated Rexmann As long as you assume 1 year is always 365 days =INT(A1/365)&" year(s) "&MOD(A1,365)&&q...

Can I display the current date in a text box?
I know how to display the current date in a cell, but can I display it in a text box? And how would I do that? You would have to have some code to load it, such as Textbox1.Text = Format(Date,"dd mmm yyyy") or link the texbox to a cell with the formula =TODAY() -- HTH RP (remove nothere from the email address if mailing direct) "stephiebrady" <stephiebrady@discussions.microsoft.com> wrote in message news:C78C4C78-C12C-4A8F-9121-E377ACAE3B5B@microsoft.com... > I know how to display the current date in a cell, but can I display it in a > text box? And ...

Button on Form to Run Report based on Query with Parameters
Hi Guys. I am busy working on a stock control database (and job control). When I have a Form Open, it shows several fields, including JobID (Which is the PK for that job) (frm_Invoicing) I also have a Query that when Run, it prompts for the JobIDNo. (qry_CustJobsInvStock) I have a report based on that query. (rpt_CustJobsInvStock) I have added a button onto the form (onclick -> DoCmd.OpenReport) to open and automatically print the report, But I just cannot figure out how to get it to get my JobID from the form, and to Automatically add it as a Parameter for the que...

Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & DATEDIF(C6,NOW(),"md") & " D" I'd like a formula that can produce the same format (years, months, days) between now and a future date. Any ideas? Thanks in advance, Bart Hi Bart Try this with the date in A2 =IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...

Advanced Find should let me search records between two dates
When searching the system for records, many times I need to search for records that came in between two dates. For instance, I would like to be able to pull all records input in CRM between March 1 and March 15. In 3.0 you can only query specific dates like "Last X days," "Last X Months," "On," "On or After," etc., but you cannot search the date fields between two dates. The functionality was available in 1.2 but is not available in 3.0. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the...

Sort search results by date
It would be nice if i could sort my search results in this forum by date. I would like to see my most recent questions at the top. -- Sheri Salomone THANKS! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroup...

Auto insert of date
I'm trying to input some datas using preparedStatement and among those coiumns one column is of type "smalldatetime" i wish to input the current date and time at which i execute this query, how can i achieve this in JDBC or what is the sql query for this. I'm not sure if we're talking JET SQL or T-SQL or something else here? In JET SQL you want the Date() function, something like ... INSERT INTO Table1 ( TestDate ) SELECT Date() AS Expr1; If you're using T-SQL you want the GETDATE() function, something like ... INSERT INTO dbo.Table1 (Test...

Use specific printer for reports in view mode
I designed a report using printer HP5000 PLC6 and the paper size is A3 in landscape. When I open the report in my laptop and I want to show the report in my resentation, it can not showed all contents of the reort, just a hals is shown, my question is how can we assign this printer to this reprot when I open it, although I do not need to print it, this is just for my resentation. My printer default in mypc is lHP desckjet 690c -- H. Frank Situmorang Might try installing the printer driver for the HP5000 PLC6. Might try suing the printer "Microsoft Office Document Image Writer&qu...

Matching Zip coded in a Query
I have query that joins two tables using the Zip Codes (only show the records where the Zip codes match). The problem is that in one table, the listing of zip codes, it is 5 digit zip codes. In the main table some of the zip codes are 5+4 zip codes. My current query leave out the 5+4 Zip codes. I want all the records as long as the 5 digit zip codes matches. I quess I could delete the - and the last 4 digits on the main table, but it would be nice to retain the whole zip codes. Is there a way to do this? Thanks!!!!! Use Left(MainTableZipCodeField,5) to compare to the original ...

Hold a cell range for an "average formula"
I add a column every week to my work sheet and want the formula to always calculate the current 5 column range. Wendy Akers wrote: > I add a column every week to my work sheet and want the formula to always > calculate the current 5 column range. > > Add a column WHERE? What "formula"? Where is the "current 5 column range"? Hi, Let's say your data is in range C3:H3. In cell K3, enter the following array formula (Ctrl+Shift+Enter) to get the average =AVERAGE(OFFSET(INDIRECT(ADDRESS(3,MAX(ISNUMBER(C3:J3)*COLUMN(C3:J3))+1)),0,...

how do i subtract one date from another and get an age in years
i am trying to subtract a date of birth from today's date and get an age in years. Can anyone help me? http://www.cpearson.com/excel/datedif.htm -- Kind Regards, Niek Otten Microsoft MVP - Excel "ucastores" <ucastores@discussions.microsoft.com> wrote in message news:FE331F27-C314-42D4-A390-9DA97E0E493D@microsoft.com... >i am trying to subtract a date of birth from today's date and get an age in > years. Can anyone help me? > Hi see: http://www.cpearson.com/excel/datedif.htm#Age -- Regards Frank Kabel Frankfurt, Germany "ucastores" &l...

Queries and reports in access Need help please
Hello everyone , I have a little issue to setup a database. I created a table with approximately 20 columns. The columns have an entry of Yes or no base on a questionnaire from survey. What can I do to get a result of my table I would like to know how many "Yes" and "no" I have. I don't know how to add them or get this information. Thanks in advance GABRIEL Gabriel, The first answer to your question is that your table structure is not really the best for this situation, and it will be a lot easier if you can set it up differently. Is your database design set in ...

calculating dates #2
What formula do i use??? here is my problem... i have 3 columns A- Qty of days eg: 6, B-start date, eg: 10/12/07 C -end date, , (formula i am using, (colummn (C) =A+B+1 (copied down the column) this gives me an end date by adding A and B which i use in a gantt chart, eg; "but" some dates do not have a start date yet and the end result will read (C) 05/01/00, how can i get column (C) to remain blank until i put a start date in???? =IF(A1="","",A1+B1+1) -- Kind regards, Niek Otten Microsoft MVP - Excel "spudpeeps" <spudpeeps@discussions.mic...

Formatting the Date in Excel
Is there any way in Excel that I can format the Date so I don't have to type any slashes between the month and day? In other words, I would like to be able to type in 0714 press "enter" and have excel recognize that this is 07/14/2005 without typing in 07/14. Any suggestions would be appreciated. Thanks, Short of writing code, you could enter your numerics only date in cell A1, for instance: 07142005. Then in B1 write a formula: =mid(A1,1,2)&"/"&mid(A1,3,2)&"/"&left(A1,4) Then you could copy column B and paste as values into their req...

Query repeated values
Hello everyone. Sorry for such a newbie doubt, but since my Access and SQL experience is a bit “trial and error” I don’t even know what to search for here in the forums that so I can help myself. I have two tables A and B. First table has people’s NAMES field. Marc Wilson Andrea Smart Francis Junior Second table has a field with people’s ABILITIES but some are repeated: WORD – Marc Wilson WORD – Andrea Smart EXCEL – Francis Junior COREL – Andrea Smart COREL – Andrea Smart I’m trying to build a query to point out the duplicated lines, in this case: COREL – Andrea Smart ...

Date field behavior differs between forms
I have two forms with seemingly identical date fields. In one, if I place my cursor in the middle of it, the first number I type gets put in the far left of the field. In the field on the other form, if I place the cursor in the middle, it starts typing right where I am. Any ideas? I have thoroughly looked through the properties of each field and each form and I cannot find what is causing this behavior. I would love to get both of them to start placing typed characters at the far left instead of where the cursor is. Thanks! Check the Text Alignment property of both controls (not ...

union queries
I need to build a query which combine records from a number of similar tables. Building a union query works great with "normal" records. Problem is, these tables contain attachments in some fields and MS ACCESS 2007 treat these fields as multi-value fields and does not allow building APPEND or UNION queries with multi-value fields. I am so frustrated with this, because I would like to base my searches and some other queries on this. PLEEEESe help Cheers Dawie Theron On Fri, 26 Feb 2010 05:54:01 -0800, Dawie Theron <DawieTheron@discussions.microsoft.com&g...

Won't open a specific pdf attachment
Outlook stopped opening a pdf file that I have been receiving on a daily basis for several months. About a week ago I started having propblems opening the pdf file now when I click on the e-mail with the attachment it says it can't open the attachment and then opens the e-mail but there is no attachment. I do not have any problems opening e-mails with other attachments or other e-mails with pdf attachments even from the same vendor. It is just one specific type of file with the same name which comes each day. Any ideas Thanks, Peter .. ...

2 queries, a date range and a report?!
I'm pretty much a novice access user, and am designing my DB with the frequent assistance of these forums. However I'm now trying to do something that I can't find any reference to - Can anyone advise? I have a report that is to be printed from a form using a print control button. The report consists of 2 queries which look up 2 different types of "item" by date range. I want to be able to prompt the user once for the start and end date. If I put a start/end request on both of the queries, then the user has to enter the dates twice, and when you choose...