By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Current Age from birthday field

P: 5
I need a box on my form that computes a person's age using their birthday field entered on the same form (and stored in the table). The current age doesn't need to be stored in a table. I just want it to be visible so that I don't have to do the figuring in my head. Suggestions on the expression that would be needed to show the current age?

I am using Access 2003 with Windows XP in English.

Thanks, Tim
Oct 27 '06 #1
Share this Question
Share on Google+
10 Replies


P: 3
Hey, take a look at the datediff function. If you search the help file, something should come up... Maybe something like:

lbl_age = datediff('yyyy', cboBirthDate, date)

for more info/syntax, check out:
http://www.techonthenet.com/access/functions/date/datediff.php

Hope this helps!

I need a box on my form that computes a person's age using their birthday field entered on the same form (and stored in the table). The current age doesn't need to be stored in a table. I just want it to be visible so that I don't have to do the figuring in my head. Suggestions on the expression that would be needed to show the current age?

I am using Access 2003 with Windows XP in English.

Thanks, Tim
Oct 27 '06 #2

PEB
Expert 100+
P: 1,418
PEB
And also I can say that it's better to check the Access forum this post is largely discussed

:)
Oct 27 '06 #3

P: 14
The following works for me (in Access 2002) -- my Date-Of-Birth field is called "dob". This formula prevents "error" strings from appearing in the calculated age field when the dob field hasn't yet been entered or is invalid.

Expand|Select|Wrap|Line Numbers
  1. =IIf(Nz([dob],"")="","",DateDiff("yyyy",[dob],Now())+(Now()<DateSerial(Year(Now()),Month([dob]),Day([dob]))))
  2.  
Hope this helps,

radio1
Oct 27 '06 #4

P: 5
Radio, thanks for the coding, it worked fine just the way you had it with my fireld change.

Blessings,
timgeitz
Oct 28 '06 #5

P: 14
You're welcome.

Please note that there are possibly "bigger/better/stronger/faster" ways of doing it -- I'm still quite new at Access and VBA coding. You just happened to ask the one question in the entire forum that I was apparently qualified to answer!

Cheers,

-radio1
Oct 28 '06 #6

NeoPa
Expert Mod 15k+
P: 31,347
That's how it works for all of us radio1.
(Excluding MMcCarthy of course who's just a posting machine of all known Access issues - My hat is permanently off ;-) )
Just reply to the ones that we can help on, which is what you did - so ups to you on that.
Oct 28 '06 #7

PEB
Expert 100+
P: 1,418
PEB
Hi,

First the function

IIf(Nz(#23/03/2000#,"")="","","") a test like this gives me an error:
wrong number of arguments for the function nz()

Nz() is used like : nz(#23/03/2000#) or nz("23/03/2000") only with one argument...

But this part of the function isn't important coz it verifies if there is a birthdate introduced... So keep a part...

The rest of the function is:

DateDiff("yyyy","23/03/2000",Now())+(DateSerial(Year(Now()),Month("23/03/2000"),Day("23/03/2000")))

With string values it doesn't give me correct results...

with date values:

the same result...

But the function DateDiff("yyyy","23/03/2000",Now()) gives just the age without monts and days...

But after some verification you can use this one:

=str(Datediff("yyyy",Cvdate(Format("23/03/2000","DD/mm/yyyy")),Now()))+"/"str(Datediff("m",Dateserial(Year(Now()),Month(Cvd ate(Format("23/03/2000","DD/mm/yyyy"))),Day(Cvdate(Format("23/03/2000","DD/mm/yyyy")))),Now()))+"/" +str(Datediff("d",Dateserial(Year(Now()),Month(Now ()),Day(Cvdate(Format("23/03/2000","DD/mm/yyyy")))),Now()))

Have a nice evening!

The following works for me (in Access 2002) -- my Date-Of-Birth field is called "dob". This formula prevents "error" strings from appearing in the calculated age field when the dob field hasn't yet been entered or is invalid.

Expand|Select|Wrap|Line Numbers
  1. =IIf(Nz([dob],"")="","",DateDiff("yyyy",[dob],Now())+(Now()<DateSerial(Year(Now()),Month([dob]),Day([dob]))))
  2.  
Hope this helps,

radio1
Oct 28 '06 #8

PEB
Expert 100+
P: 1,418
PEB
radio 1 10x for the idea...

it was a very good way to find a short way to calculate it..

Before there was threads there , where we with mcarthy have created a long calculation functions about this...

And I wasn't able to begin a thread like the previous one....

But it was a very good idea...

:)
Oct 28 '06 #9

NeoPa
Expert Mod 15k+
P: 31,347
The optional second parameter to the Nz() function specifies the value to return if the first parameter evaluates to Null.

Thus
Expand|Select|Wrap|Line Numbers
  1. Nz([A], [b])
is equivalent to
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([A]), [b], [A])
Oct 28 '06 #10

PEB
Expert 100+
P: 1,418
PEB
I'm afraid but It gives me error on My Access...

And don't accept this optional argument...

But in this case it isn't a problem! It's unsignifcant for the function that calculates the age :)

So discuss it in different thread???
:)

The optional second parameter to the Nz() function specifies the value to return if the first parameter evaluates to Null.

Thus
Expand|Select|Wrap|Line Numbers
  1. Nz([A], [b])
is equivalent to
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([A]), [b], [A])
Oct 28 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.