query help

[Guest]

ok guys here's the problem. i have a table with a DOB field in it, and would
like to use a query to calculate an employee's age from the DOB field. Here's
the code i'm using:

Age:
IIf(Date()<DateSerial(Year(Now()),Month([dob]),Day([dob])),DateDiff("yyyy",[dob],Now()-1,DateDiff("yyyy",[dob],Now())))

It works to a certain extent, but for example when "05/051986" is entered it
outputs "19" as the age, which isn't true: it should be "18". Can someone
please explain what is wrong with the code, or give me the correct code. I am
also in need of a speedy response if possible. thanks.

 

[Douglas J. Steele]

That can't have been copied from your application, because it's invalid as
written.

You've got 4 parameters in the first DateDiff function.

I believe what you need is:

IIf(Date()<DateSerial(Year(Now()),Month([dob]),Day([dob])),DateDiff("yyyy",[dob],Now())-1,DateDiff("yyyy",[dob],Now()))--Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)"Joe Robbo" <Joe (e-mail address removed)> wrote in messagenews:[email protected]...> ok guys here's the problem. i have a table with a DOB field in it, andwould> like to use a query to calculate an employee's age from the DOB field.Here's> the code i'm using:>> Age:>IIf(Date()<DateSerial(Year(Now()),Month([dob]),Day([dob])),DateDiff("yyyy",[dob],Now()-1,DateDiff("yyyy",[dob],Now())))>> It works to a certain extent, but for example when "05/051986" is enteredit> outputs "19" as the age, which isn't true: it should be "18". Can someone> please explain what is wrong with the code, or give me the correct code. Iam> also in need of a speedy response if possible. thanks.

 

[Marshall Barton]

ok guys here's the problem. i have a table with a DOB field in it, and would
like to use a query to calculate an employee's age from the DOB field. Here's
the code i'm using:

Age:
IIf(Date()<DateSerial(Year(Now()),Month([dob]),Day([dob])),DateDiff("yyyy",[dob],Now()-1,DateDiff("yyyy",[dob],Now())))

It works to a certain extent, but for example when "05/051986" is entered it
outputs "19" as the age, which isn't true: it should be "18". Can someone
please explain what is wrong with the code, or give me the correct code. I am
also in need of a speedy response if possible. thanks.

Take a look at:
http://www.mvps.org/access/datetime/date0001.htm