Calculate age

Valery

Registered User.
Local time
Today, 16:43
Joined
Jun 22, 2013
Messages
363
Hi everyone!

I am using the expression below, in a form control, to get the age from a date of birth. In this database, it is very important that it be accurate as when the member turns 16, some paperwork has to be issued among other actions.

Expression: =DateDiff("yyyy",[txtDOB],Date())

Noticed yesterday that a member whose DOB is 20-May-2000, returned 16 years old. But in fact, he is still 15 years old with 3 months or so to go!

How difficult or simple is it to adapt this expression to be more accurate? I am a newbie of sorts...

Thank you!
 
Re: Calculate age - needed quickly

try something like

DateDiff("yyyy",[txtDOB],Date())+format(txtDOB,"mmdd")<format(Date,"mmdd")
 
Re: Calculate age - needed quickly

I also need this expression for a query. As is, it cannot work in a query but should be great in a form's control. In the query, the field name is DOB. So tried :

Age: DateDiff("yyyy",[DOB],Date())+Format([DOB],"mmdd")<Format([Date],"mmdd")

I get no error message but everyone has a zero for their age.


PS: Are you my guardian angel? THANK YOU!
 
Last edited:
Re: Calculate age - needed quickly

Mmm... Must be doing something wrong. In the form, it returns: #Name?.

For the date of birth, the control name is txtDOB and the field name is DOB.

For the age calculation, the control is called txtTenantAge (not that it matters).

=DateDiff("yyyy",[txtDOB],Date())+Format([txtDOB],"mmdd")<Format([Date],"mmdd")

I can't see the error...
 
Re: Calculate age - needed quickly

The logic in CJ's formula is correct but try in the query two changes, drop the square brackets around the date, and insert brackets around the format parts (which must be processed together first to return a numeric value rather than 2 strings)

DateDiff("yyyy",[DOB],Date())+(format([DOB],"mmdd")<format(Date(),"mmdd")
 
Re: Calculate age - needed quickly

think it is the date bit - should be date() - no square brackets

as- see cronk has already advised
 
Re: Calculate age - needed quickly

Thank you both.

The query now returns "14" but he is 13 years 8 months 13 days... so should return 13. Should this be written like "every year, on that date (DOB), add 1 to this number..." (sorry can program this, lol) or am I complicating things?
 
Re: Calculate age - needed quickly

Apologies. I just noticed that in adding the red bracket at the end, I overwrote the existing one. That is, there should be two brackets at the end.

Incidentally, you can see the effect of the two parts of the formula by putting each part in a separate column of your query

FirstPart: DateDiff("yyyy",[DOB],Date())
SecondPart: (format([DOB],"mmdd")<format(Date(),"mmdd"))
 
Re: Calculate age - needed quickly

put this in a module:

Code:
Function fnAge(dtmBD As Date, Optional dtmDate As Date = 0) _
 As Integer
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function

call it in the query:

Age:fnAge([txtDOB])
 
Re: Calculate age - needed quickly

Sorry, forgot to say it told me about the missing bracket which I did put in.

Just copied the coding you provided. The first one (FirstPart) still returns age "14".
The second one, returns 0 or -1 (not sure I understand that one).

So why is it not returning 13?
 
Re: Calculate age - needed quickly

Dear Arnelgp,

1) I pressed ALT F11. Copied the coding you generously provided. I closed the "editor". I can't see the module group in the navigation pane. I checked in Options, everything was/is selected - nothing to be hidden. I also used to be able to see all modules created in other database I worked with...

I tried doing it again but it is calling it Module2. So obviously, Module1 is somewhere and was saved.

2) I put in the coding in the query Age:fnAge([txtDOB]). It returns the following error message:

The expression contains an ambiguous name. Verify that each name in the expression refers to a unique object.

And fnAge is highlighted after I close the error message.

Please help. A module sounds like a great idea.
 
Re: Calculate age - needed quickly

remove module2, the code is already saved in module1 that is why it is complaining. on vbe, right click module2 and choose remove.
 
Re: Calculate age - needed quickly

If I could see it, I would. As I said, it is not showing in the navigation pane. I don't know how to get to it. I looked on the Web for a solution... can't find one. The options settings are set to show ALL.
 
Last edited:
Re: Calculate age - needed quickly

Got it! Went into Create Module - then View - then Project Explorer. Was able to delete Module 2. So error message is gone :). AND, 13 is showing!! :)))))

However, can something be done for:

1) When trying to sort on that column, I get "Data type mismatch in criteria expression.
2) There is an "error" wording in the field where the DOB field is blank (unfortunately some people still have not provided their date of birth).

If number 2 can't be "fixed" (not really an error) - it's ok. Just weird to see error...

THANK YOU
 
Re: Calculate age - needed quickly

here is the fix:
Code:
Function fnAge(dtmBD As Variant, Optional dtmDate As Date = 0) _
 As Variant
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If IsNull(dtmBD) Then Exit Function
    If Not IsDate(dtmBD) Then Exit Function
    dtmBD = CDate(dtmBD)
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    fnAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function
 
Re: Calculate age - needed quickly

Great! Error word gone. However the sort goes 1 10 to 20 than 2 ... I set the DOB as Date/Time, Medium Date (guess that can be the problem, right?).

THANK YOU - this is so great! I think many people will want this. I certainly will share it.
 
Last edited:
Re: Calculate age - needed quickly

Also, may I impose on you for something else? I wrote some code (took a while, a long while to figure out!!!) in control text boxes for a given date to change into French. It works. However, each time I need a French date, I have to go back to this "sample" report, copy all the controls, adapt them to reflect the new date field I want in French...

Prior to this I had gone through the Forum and found a guy who wanted the same thing. One member ended up telling him to write a function. His answer was "Great, did a function, works fine". But :( he didn't provide the function for the rest of us.

I replied to the thread asking for the function but never got an answer. Also, the thread was old.

Can I show you my "coding"? Should I open a new thread?

Thank you!
 
Arnel,

Can we ad the following in the function? Would be sooooo much better than the conditional formatting I set everywhere.

When the age turns to 16, the following field should be set to "yes" (ticked). There are and will be no exceptions to this.

Table: tblTenants
Field: Maillist - format: yes/no

Thank you!
 
on sorting, cast the return value from our function into an integer. on your sql:

Age:IIf(IsNull(fnAge([DOB])),Null,CInt(fnAge([DOB]))

the other issue, you can create new post.
by the way, how do you want your date to display. french (i see). but how do you format it. is it like yyyy-mm-dd?
 
do you really want that, i mean save it to a field in a table. we can just compute that on a fly.
anyway, if you really like that create a public function in a module (any module,, or module1).

public function fnUpdateMailListField()
dim db as dao.database
set db = currentdb
db.execute "Update tblTenants Set [MailList] = True Where [MailList] = False And " & _
"Val(fnAge([DOB]) & '') > 16;"
set db=nothing
end function

now call this in Autoexec macro, using runcode. so each time you open the db, this code will run.
 

Users who are viewing this thread

Back
Top Bottom