Date/Age

[Guest]

I have two fields in a form, one [NoYrs] (the number of years] and one
[StartDate] where I enter a specific date. I have another field that I want
to be the result of the StartDate plus the NoYrs entered. How would I do
that?

Then...

I want to take the resulting date from above=[FinishDate] and subtract
customer's DOB, giving me the age of the customer at the finishdate. How
would I do that?

 

[Douglas J. Steele]

To add a number of years to a given date, use the DateAdd function:

DateAdd("yyyy", [NoYrs], [StartDate])

To get an age, given a DOB and a particular date, use:

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

 

[Douglas J. Steele]

Just realized that the customer's age can be calculated a little more simply
as:

[NoYrs] - IIf(Format([FinishDate], "mmdd" < Format([DOB], "mmdd", 1, 0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

To add a number of years to a given date, use the DateAdd function:

DateAdd("yyyy", [NoYrs], [StartDate])

To get an age, given a DOB and a particular date, use:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have two fields in a form, one [NoYrs] (the number of years] and one
[StartDate] where I enter a specific date. I have another field that I
want
to be the result of the StartDate plus the NoYrs entered. How would I do
that?

Then...

I want to take the resulting date from above=[FinishDate] and subtract
customer's DOB, giving me the age of the customer at the finishdate. How
would I do that?

 

[Guest]

I got the DateAdd part to work. However, I'm having trouble with the next
part. It says that the expression is missing ()'s and when I think that I've
fixed that it says that there is an incorrect number of arguments.

I should also mention that the customer's DOB isn't found on this form.
It's found on another form [Customers] bound to table [CustTable].

Can you help get this figured out? Thanks!

To add a number of years to a given date, use the DateAdd function:

DateAdd("yyyy", [NoYrs], [StartDate])

To get an age, given a DOB and a particular date, use:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have two fields in a form, one [NoYrs] (the number of years] and one
[StartDate] where I enter a specific date. I have another field that I
want
to be the result of the StartDate plus the NoYrs entered. How would I do
that?

Then...

I want to take the resulting date from above=[FinishDate] and subtract
customer's DOB, giving me the age of the customer at the finishdate. How
would I do that?

 

[Douglas J. Steele]

I have no idea what I was thinking when I typed that: I did miss some
parentheses:

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

or, as I suggested in a follow-up post,

[NoYrs] - IIf(Format([FinishDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

If the Customers form is open, you can refer to a field on it as

Forms!Customers!DOB

or

[Forms]![Customers]![DOB]

If it's not open, you'll need to use something like DLookup to retrieve the
value from the CustTable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I got the DateAdd part to work. However, I'm having trouble with the next
part. It says that the expression is missing ()'s and when I think that
I've
fixed that it says that there is an incorrect number of arguments.

I should also mention that the customer's DOB isn't found on this form.
It's found on another form [Customers] bound to table [CustTable].

Can you help get this figured out? Thanks!

To add a number of years to a given date, use the DateAdd function:

DateAdd("yyyy", [NoYrs], [StartDate])

To get an age, given a DOB and a particular date, use:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have two fields in a form, one [NoYrs] (the number of years] and one
[StartDate] where I enter a specific date. I have another field that I
want
to be the result of the StartDate plus the NoYrs entered. How would I
do
that?

Then...

I want to take the resulting date from above=[FinishDate] and subtract
customer's DOB, giving me the age of the customer at the finishdate.
How
would I do that?

 

[Douglas J. Steele]

Check the Help file, but essentially it's DLookup("FieldName", "TableName",
"WhereCondition")

WhereCondition is a normal Where statement, without the keyword "Where" at
the beginning.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The other form won't be open to retrieve the DOB. I'm not quite sure how
to
do the DLookup. Could you point me in the right direction? Thank you so
much!

I have no idea what I was thinking when I typed that: I did miss some
parentheses:

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

or, as I suggested in a follow-up post,

[NoYrs] - IIf(Format([FinishDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

If the Customers form is open, you can refer to a field on it as

Forms!Customers!DOB

or

[Forms]![Customers]![DOB]

If it's not open, you'll need to use something like DLookup to retrieve
the
value from the CustTable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I got the DateAdd part to work. However, I'm having trouble with the
next
part. It says that the expression is missing ()'s and when I think
that
I've
fixed that it says that there is an incorrect number of arguments.

I should also mention that the customer's DOB isn't found on this form.
It's found on another form [Customers] bound to table [CustTable].

Can you help get this figured out? Thanks!

:

To add a number of years to a given date, use the DateAdd function:

DateAdd("yyyy", [NoYrs], [StartDate])

To get an age, given a DOB and a particular date, use:

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

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have two fields in a form, one [NoYrs] (the number of years] and
one
[StartDate] where I enter a specific date. I have another field
that I
want
to be the result of the StartDate plus the NoYrs entered. How would
I
do
that?

Then...

I want to take the resulting date from above=[FinishDate] and
subtract
customer's DOB, giving me the age of the customer at the finishdate.
How
would I do that?