Calculate age in years and months from birthdate?
Bucksy
control) that will calculate the age in years and months from the birthdate.
MS has a sample that does years.
Bucksy
Douglas J. Steele
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Bucksy" <Buc...@discussions.microsoft.com> wrote in message
news:BDFB9AAA-A8DC-4845...@microsoft.com...
Suzy
Jeff Conrad
You can find him here:
http://www.accessmvp.com/djsteele/Diff2Dates.html
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html
"Suzy" wrote in message:
news:48374B7F-4FB1-41B9...@microsoft.com...
Suzy
I'm a beginner user, muddling my way through things I really don't
understand!
I have a table with staff database with a column for "birthdate" and I am
wanting to report the age in years and months within a report showing each
individual's personal details.
Me thinks I'm in over my head!
Jeff Conrad
1. Make a backup of your database (always a good thing).
2. Copy/paste all of that code on Doug's page to a new standard
module. Just copy the part starting on the line that says:
'***************** Code Start **************
....and down to the bottom.
3. Compile the code, save and close the module. Name the module
basDougDateFunction
4. Now in your report I assume you already have a field in the recordsource
called Birthdate. Add a new unbound textbox into the Detail section.
Position it where you want and call it txtAge.
5. Enter this into the Control Source for txtAge:
=Diff2Dates("ym",[Birthdate],Now())
What this will do is display the age in years and months based on the
entry in the Birthdate field.
6. Save and close the report and then test.
You should be good to go.
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html
"Suzy" wrote in message:
news:73ECA8A3-4DE0-48EC...@microsoft.com...
Douglas J Steele
Witness Protection Program, you weren't supposed to reveal my whereabouts!
<g>
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Jeff Conrad" <je...@ernstbrothers.com> wrote in message
news:OmwgvtFr...@tk2msftngp13.phx.gbl...
Jeff Conrad
news:e%23nD$uIrFH...@TK2MSFTNGP15.phx.gbl...
> Thanks for covering for me, Jeff. However, I thought you knew that under the
> Witness Protection Program, you weren't supposed to reveal my whereabouts!
> <g>
Oops, sorry.
Better not unpack Doug.
;-)
Suzy
Jeff Conrad
news:2BE3EAEC-6CE8-4B29...@microsoft.com...
> Hi Jeff and Doug, this works perfectly! Thank you SO much for your help.
You're welcome, glad we could help.
Smohrman
I am looking to implement the solution you helped Suzie with, and am having
a little trouble past a couple of steps. I have a database in which I need
to calculate the same thing, and I have created the module per your
instructions, but I don't know what you mean by "a new unbound textbox into
the Detail section. Position it where you want and call it txtAge. Enter
this into the Control Source for txtAge..."
I'm trying to include this function in a table, and don't understand the
steps to create the text box, and then where to enter the Control Source
info. I'm almost there! Can you point me in the right direction from where
I'm at? Anyone?
Thanks in advance...
Scott
fredg
You DO NOT do this in any table.
If you store the Age then it is sure to be incorrect at least once a
year.
Table are used to store data, not to perform calculations.
As long as you have the date of birth stored, you can compute the age
as instructed, in a form, query or report, as Jeff stated.
You do it in the report itself.
Add an UNBOUND text control to your report (Click on the ToolBox tool
button. Click on the Text control (marked Ab). Drag it onto your
report.
Look in the Control's properties Data tab.
Click on the Control Source line.
Write whatever Jeff suggested (as it's not included in your message
here, I can't be specific).
Something like ....
= Diff2Dates(etc.)
Run the report. If you have correctly entered the Diff2Dates
arguments, you will see the age in years and months for each person in
the report.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Smohrman
gotten some code working that returns a value of months since a date:
Age in Months: DateDiff("m",[AquisitionDate],Now())
This gives us the age of the item in months. Ideally I’d like to use
something like this:
Age=DateDiff("yyyy", [AquisitionDate], Now())+ _
Int( Format(now(), "mmdd") < Format( [AquisitionDate], "mmdd") )
which should give the age in Years/Months, but when I try it I get an error
stating that I “Omitted an operand or operator, used an invalid character or
comma, or didn’t surround text with quotes...” I tried changing Age= to Age:
DateDiff like in the code above which works but that didn’t fix it.
Thanks for your help.
James A. Fortune
As an alternative, the logic from
http://groups.google.com/group/microsoft.public.access/msg/cf857b8e3aa9c6b0
can be used if you replace DateAdd('d', N, Date()) with the final date
and Date() with the BirthDate. Note that the 'AS' keyword in SQL is
used to give an expression a name in a way that is equivalent to using a
name with a colon after it in the QBE query design window.
James A. Fortune
MPAP...@FortuneJames.com
fredg
See comments interspersed below as well as at the end of this message.
> Thanks for that clarification. I am now working with a query, and have
> gotten some code working that returns a value of months since a date:
>
> Age in Months: DateDiff("m",[AquisitionDate],Now())
>
> This gives us the age of the item in months. Ideally I’d like to use
> something like this:
>
> Age=DateDiff("yyyy", [AquisitionDate], Now())+ _
> Int( Format(now(), "mmdd") < Format( [AquisitionDate], "mmdd") )
> which should give the age in Years/Months,
No it will not give you the age in years and months.
And that is not how you write and expression in a query.
> but when I try it I get an error
> stating that I “Omitted an operand or operator, used an invalid character or
> comma, or didn’t surround text with quotes...” I tried changing Age= to Age:
> DateDiff like in the code above which works but that didn’t fix it.
If you are in the query grid you do not start with the = sign. Use:
Age:DateDiff(etc....)
More comments at the end.....
1) To accurately compute Age by year, you must take into account
whether or not that date has already occurred in the current year.
DateDiff by itself does not take that into account, i.e.
DateDiff("yyyy",#12/31/2005#,#1/1/2006#) will = 1.
For someone born on 9/15/1980, the DateDiff function will return
either years, (26), or months, (305), or days ( 9271), depending upon
the argument you supply it. (Note that the actual age in years should
be 25, not 26, as the birth date has not yet occurred this year).
To accurately calculate someone's age in years only, the following
will be accurate.
In a query:
Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)
2) You are using Now() to represent the current date. Now() includes a
time of day value and you may get different results depending upon the
actual time of day you run the query. Always use Date() if the time of
day is not needed.
3) The post I responded to had a reference to Doug Steele's Diff2Dates
function at:
http://www.accessmvp.com/djsteele/Diff2Dates.html
not the DateDiff function.
The Diff2Date function will return the age in years and months and
days, all at once, depending upon the arguments you supply it.
Some one born on 9/15/1980 will be
"25 years 4 months 18 days" old today.
Isn't that what you want according to your message subject line?
If you want that then you must use the Diff2Dates function.
Download the function and copy it to a Module.
Refer to it in your query grid like this:
AgeToday:Diff2Dates("ymd",[DateOfBirth],Date(),True)
or like this (notice the different results):
AgeToday:Diff2Dates("ymd",[ADate],Date())
Good luck!
Smohrman
Appreciate your time :-D
Lynn
Visual Basic. I built a query to determine a person's age per the above
instructions. It works perfectly (thank you). I copied the SQL code and
put it in my VB code after "myRecordset.open." I want it to fill the textbox
(in VB) with the person's age. Now it gives me the error: "Item cannot be
found in the collection corresponding to the recordset." I have other
databases that work fine in VB and fill out textboxes just fine. I
triple-checked the spelling of the field names, etc., and they all match.
Can anyone help?
Thanks,
Lynn
Douglas J. Steele
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Lynn" <Ly...@discussions.microsoft.com> wrote in message
news:A404F587-830F-4419...@microsoft.com...
Lynn
Thank you so much for getting back to me. I spent all day yesterday reading
about Access queries. I program in VB6 and have very little experience in
Access. Here is my code:
Dim myConnection As ADODB.Connection
Set myConnection = New Connection
Dim myRecordset As New ADODB.Recordset
myRecordset.ActiveConnection = myConnection
' Open PatientInfo Query
myRecordset.Open "SELECT PatientInfo.PatientID, PatientInfo.Birthdate,
Diff2Dates('ymd',[Birthdate],Now()) AS Age FROM PatientInfo", dBase,
adOpenDynamic, adLockOptimistic
myRecordset.Filter = "(PatientID) like '" & strPatientID & "*'"
frmPatientInfo.txtAge.Text = rs![Age]
myRecordset.Close
I have the "Open" statement on one line. I set a break at rs![Age] and it
gives me that "Item cannot be found..." error. Can you please tell me what I
am doing wrong? I'm curious, how does VB know what query to look in? I see
where it refers to the table, but not the query. Any help would be greatly
appreciated!
Lynn
Douglas J. Steele
name the module? If you named the module Diff2Dates, rename it: modules
can't be named the same as functions or subs in them.
I'm assuming you're actually running this code from within Access, not from
a VB program. You can't put user-defined functions into Access queries if
you're running them from outside of Access.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Lynn" <Ly...@discussions.microsoft.com> wrote in message
news:97C59C22-4117-492C...@microsoft.com...
Lynn
No, I am running the code within VB. I guess that was my main question. I
have it in a "Select" event. I use the SQL statement that the query
generates and I put it in VB after: myRecordset.Open
My Access book says, "You don't have to base a recordset on a single table.
You can base it on a query. But you can't use the query's name in the
myRecordset.Open statement because only table names are allowed." It then
says to get the SQL statement from the query and paste it into VB after the
myRecordset.Open and inbetween the double quotes.
So that's what I did. But then I get that error. I know I am totally
confused here, so I hope I'm not confusing you. One more thing: I am not
putting a user-defined function into the Access query. The query works
perfectly. It generates the person's age by year, month and day. I just
need to translate that into VB so that it will populate the text box.
Thanks again!
Douglas J Steele
I'm not even sure what you mean by "running the code within VB". I'm
assuming you mean VB6 (some people refer to code in Access as VB, even
though it's actually VBA)
Are you using a Jet database (an MDB) to hold the data, or are you going
against some other DBMS? If you're strictly going against Jet, DAO may make
more sense than ADO, and you can use a query name in the DAO OpenRecordset
method. (DAO is actually still the preferred method for dealing with Jet
databases)
The biggest error I see with your code is that you've named the recordset
myRecordset, yet you're referring to rs![Age]. That should be
myRecordset![Age]
Of course, there are other things that I don't understand about your code:
you instantiate myConnection, but you never open it, so that there's no way
for ADO to know where it's supposed to look for its data!
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Lynn" <Ly...@discussions.microsoft.com> wrote in message
news:60079CAB-8D8E-44B4...@microsoft.com...
Brendan Reynolds
In addition to the points that Doug raises, if the book says that, then
perhaps it is the book that is the source of confusion, as that statement
has no basis in reality - you most certainly can use the name of a saved
query with the Open method of an ADO recordset. Perhaps you've just been
reading the wrong book.
A further potential source of confusion is that in your most recent post you
say that you are not using a user-defined function in the Access query, but
the SQL you posted in the previous post includes a call to Doug's
user-defined function 'Diff2Dates'. As Doug says, that will only work within
Access, and I don't think your code is running within Access, as you mention
a 'Select event', and no built-in Access, ADO, or VBA object has a Select
event.
--
Brendan Reynolds
Access MVP
"Lynn" <Ly...@discussions.microsoft.com> wrote in message
news:60079CAB-8D8E-44B4...@microsoft.com...
Lynn
I am so sorry, guys! I know so little about Access that I shouldn't even be
posting a question until I know a bit more. I really appreciate your help
and the fact that you didn't give up. This is way over my head, so I will
just drop it for now.
To answer your question, though: I am working in Visual Basic 6. I was
just trying to get the "Age" field from my Access query to populate a text
box in VB6. All my other Access table fields populate my VB6 text boxes just
fine. I think it's because I am working with a query and not a table that
it's not working. Again, I apologize.
Lynn
Brendan Reynolds
difficulty of struggling with an unfamiliar application or technology at
times - I know I have.
--
Brendan Reynolds
Access MVP
"Lynn" <Ly...@discussions.microsoft.com> wrote in message
news:D6E16EFB-D7BF-4798...@microsoft.com...