Acces 2003: Substraction between date of birth and system date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want subtract a person's date of birth form the system date (2006). I want
only the amount of years display, for example 21/01/1985 = 25

Thanx

Johan
 
The most reliable way to compute the age is to take into account whether or
not their birthday has already occurred:

DateDiff("yyyy", [DOB], Date()) -
IIf(Format$(Date, "mmdd") < Format([DOB, "mmdd"), 1, 0)

This is necessary because Access is a little too literal when it calculates
DateDiff between two dates. DateDiff("yyyy", #12/31/2005#, #1/1/2006#) will
return 1, implying that the difference between those two dates is a year.
 
Hi Douglas

Thanx for the reply. I'm new to Acces so I'm still learning. I've type the
formula like this, is this correct:
=DateDiff("yyyy",[Geboortedatum],Date())-IIf(Format$([Date],"mmdd")<Format([Geboortedatum],"mmdd"),1,0)

I've used in the Data tab at Control source. Geboortedatum (Date of Birth)
is the name of the column in the Tablet. When I use the above formula it
gives me the following answer: #Name?

Any further help please.

Thanx
Johan
 
It's probably the square brackets around Date in the first Format statement.
Try

=DateDiff("yyyy",[Geboortedatum],Date())-IIf(Format$(Date(),"mmdd")<Format([Geboortedatum],"mmdd"),1,0)

(It may be my fault: I believe I left the parentheses off in my original
answer)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Johan said:
Hi Douglas

Thanx for the reply. I'm new to Acces so I'm still learning. I've type the
formula like this, is this correct:
=DateDiff("yyyy",[Geboortedatum],Date())-IIf(Format$([Date],"mmdd")<Format([Geboortedatum],"mmdd"),1,0)

I've used in the Data tab at Control source. Geboortedatum (Date of Birth)
is the name of the column in the Tablet. When I use the above formula it
gives me the following answer: #Name?

Any further help please.

Thanx
Johan
Johan said:
I want subtract a person's date of birth form the system date (2006). I
want
only the amount of years display, for example 21/01/1985 = 25

Thanx

Johan
Click to expand...
Click to expand...
 
Thanx it works perfectly

Douglas J. Steele said:
It's probably the square brackets around Date in the first Format statement.
Try

=DateDiff("yyyy",[Geboortedatum],Date())-IIf(Format$(Date(),"mmdd")<Format([Geboortedatum],"mmdd"),1,0)

(It may be my fault: I believe I left the parentheses off in my original
answer)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Johan said:
Hi Douglas

Thanx for the reply. I'm new to Acces so I'm still learning. I've type the
formula like this, is this correct:
=DateDiff("yyyy",[Geboortedatum],Date())-IIf(Format$([Date],"mmdd")<Format([Geboortedatum],"mmdd"),1,0)

I've used in the Data tab at Control source. Geboortedatum (Date of Birth)
is the name of the column in the Tablet. When I use the above formula it
gives me the following answer: #Name?

Any further help please.

Thanx
Johan
Johan said:
I want subtract a person's date of birth form the system date (2006). I
want
only the amount of years display, for example 21/01/1985 = 25

Thanx

Johan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top