I am trying to figure out how to make an update event that will allow people in my database to be added to a list when they reach a certain age. I have their age updated automatically and when they reach 90+ I want a field to read "Y" so they will be added to this list. Anyone have any ideas? I keep trying different codes, but I don't seem to have it quite right. If it helps any, age is calculated by a datediff expression (=DateDiff("yyy y", [DOB], Now())+ Int( Format(now(), "mmdd") < Format( [DOB], "mmdd") )). When that age equal 90 or greater, I want the list field to get a "Y".
-
-
Kate137
In a properly normalized database, you wouldn't have an update query to add these people to another list/table.
Instead, you would have a database wherein the member's birthdate would be tied to that member. You would then use a query to pull the data for those individuals that meet your criteria and either review it in the query-view or as a report.
Personally, I use this: Age() Function; Allen Browne as a custom function within the query calculated field, About calculations in a query (ACC2003). I know, ACC2003; however, the method has not changed from then to now, I suspect this is valid even for ACC2013.
Try this out and let us know how you fare. -
To determine if an age in years has exceeded/reached a particular number when you have the date of birth you should use SQL similar to :
The code reliably returns a TRUE/FALSE value depending on whether or not the 90th birthday has been reached. This cannot be said for using DateDiff() :-(Code:(DateAdd('yyyy',90,[DateOfBirth])>=Date()) AS [Nonagenarian]
As zmbd has already stated, determining this on the fly within a query is definitely to be recommended over updating any data to reflect a constantly changing state. You know it makes sense ;-)Comment
Comment