Thanks John,
That gives me exactly what I needed. Is there a way to count the number who are over 60, etc every ten years?
My thanks for your help.
Robin Chapple
At 21/09/2016 11:07 PM, you wrote:
Glenn & Robin-
You CAN subtract one date from another in Access to get the number of days between the two, and you COULD divide the result by 365 to get a rough approximation of the number of years. But if you want to be exact, you have to adjust based on whether the current date is before or after the month and year of the birthday. Here's the expression from the website:
DateDiff("yyyy", [Bdate], Now())+ _
; Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )
That uses DateDiff you get the number of years between the birthdate and the current date, but it could be off by as much as one year. For example, if you subtract December 31, 2015 from January 1, 2016, you get one year. The second part of the expression takes care of that by adding the result of finding out whether the current month and year ("mmdd") is less than the month and year of the birth date. That's a boolean expression that will either return True (-1 when converted to an integer) or False (0). So, it SUBTRACTS one if the current month and day are less than the month and day of the birthdate. In my example above, "0101" is less than "1231", so the result is correctly adjusted.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
On Sep 21, 2016, at 2:36 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:
Robin,
If you work in both Excel and Access, working with date information can be confusing. As Duane has pointed out the Access Date() function returns the current date. Access does not have a Today() function. The Excel Date() function returns t he serial number for the y,m,d arguments you send to the function. It is common in Excel to simply subtract one date from another. As John has pointed out, we use the Access DateDiff() function to perform that sort of operation.
Glenn
From: MS_Access_Professionals@yahoogroups.com [ mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, September 21, 2016 5:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Age calculation
Robin-
Use the DateDiff expression or the Age function on the page Duane pointed out. The CalcAge Sub uses additional arguments to return its calculations, so it won't work called from a query.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
On Sep 21, 2016, at 9:16 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] < MS_Access_Professionals@yahoogroups.com> wrote:
You've missed on basically all cylinders. You are close to getting the age in days, not years. Date() returns today's date. You should also make sure DOB always contains an actual date.
Most of us use a function of expression like http://access.mvps.org/access/datetime/date0001.htm
&nb
sp;
Regards,
Duane
Get Outloo k for iOS
On Wed, Sep 21, 2016 at 8:47 AM +0200, "Robin Chapple robinski@westnet.com.au [MS_Access_Professionals]"< MS_Access_Professionals@yahoogroups.com> wrote:
I am using Access 2013.
I need to calculate a person's age, rounded to complete years, as at 'today'.
I tried this:Age:( (Today()-[DOB]) and I see an error message.
What have I missed?
Many thanks,
Robin Chapple
------------------------------------
Posted by: Robin Chapple <robinski@westnet.com.au >
------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visi t your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access _Professionals-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/l egal/us/yahoo/utos/terms/
__._,_.___
Posted by: Robin Chapple <robinski@westnet.com.au>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar