Age Calc Problem!

[JamesB]

My access database stopped calculating the age of people for some reason.
When it calculates, it shows the correct age (say 40) but in the format of
1940. I am using the difference of the last time the person took a test vs.
their date of birth. the code being used is:

=DateDiff("yyyy",[text125],[Date])

even if I change yyyy to yy it still generates a 4 digit age. I have also
looked at the input mask and no matter what changes I make to it it does not
change the output. the input mask is as follows:

99/99/0000;0;_

Any help would be appreciated as our frustration level is running high with
this problem!!!!

 

[Jeff Boyce]

James

It all starts with the data. What does your underlying data look like?
What data type is being referenced in your [text125] control (by the way,
not very informative title -- will you remember what went in that control in
six months? Will someone else know?)?

Your expression includes "[Date]". This implies that you have a control
you've named "Date" (hence, the square brackets). FYI, Access treats "Date"
(and many other terms) as a reserved word, and may NOT be using the same
meaning you're thinking of.

Can I assume that you want to take the difference, in years, between a
Date/Time value being displayed in [text125] and today's date? If so, use
Date() instead of [Date].

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JamesB]

Jeff,
Thanks for the response. This is a database I took over and did not have
a hand in building. I hope this will clear up my question.... Dob is the
text125 you are seeing and date is the date of the test.

DOB : 01/01/2000

Age: ??? Trying to calc

Date (of test): 10/12/2006

trying to calc the age field based on date of test vs. dob. If I take the
test again on say 9/9/07 it will be another record. So record 1 should say I
am 6 and record 2 should say I am 7. etc..... if I do diff of
[text125],[date] I get a 4 digit age (see previous post). If I reverse it i
get a negitave but, correct age.

James

It all starts with the data. What does your underlying data look like?
What data type is being referenced in your [text125] control (by the way,
not very informative title -- will you remember what went in that control in
six months? Will someone else know?)?

Your expression includes "[Date]". This implies that you have a control
you've named "Date" (hence, the square brackets). FYI, Access treats "Date"
(and many other terms) as a reserved word, and may NOT be using the same
meaning you're thinking of.

Can I assume that you want to take the difference, in years, between a
Date/Time value being displayed in [text125] and today's date? If so, use
Date() instead of [Date].

Regards

Jeff Boyce
Microsoft Office/Access MVP

My access database stopped calculating the age of people for some reason.
When it calculates, it shows the correct age (say 40) but in the format of
1940. I am using the difference of the last time the person took a test
vs.
their date of birth. the code being used is:

=DateDiff("yyyy",[text125],[Date])

even if I change yyyy to yy it still generates a 4 digit age. I have also
looked at the input mask and no matter what changes I make to it it does
not
change the output. the input mask is as follows:

99/99/0000;0;_

Any help would be appreciated as our frustration level is running high
with
this problem!!!!

 

[JamesB]

I figured it out. After much playing with it. I removed the input mask and
changed it a general number but, still used the formula
=datediff("yyyy",[text125],[Date]) and everthing works great!!!! Thanks for
all the help!!!!

Jeff,
Thanks for the response. This is a database I took over and did not have
a hand in building. I hope this will clear up my question.... Dob is the
text125 you are seeing and date is the date of the test.

DOB : 01/01/2000

Age: ??? Trying to calc

Date (of test): 10/12/2006

trying to calc the age field based on date of test vs. dob. If I take the
test again on say 9/9/07 it will be another record. So record 1 should say I
am 6 and record 2 should say I am 7. etc..... if I do diff of
[text125],[date] I get a 4 digit age (see previous post). If I reverse it i
get a negitave but, correct age.

James

It all starts with the data. What does your underlying data look like?
What data type is being referenced in your [text125] control (by the way,
not very informative title -- will you remember what went in that control in
six months? Will someone else know?)?

Your expression includes "[Date]". This implies that you have a control
you've named "Date" (hence, the square brackets). FYI, Access treats "Date"
(and many other terms) as a reserved word, and may NOT be using the same
meaning you're thinking of.

Can I assume that you want to take the difference, in years, between a
Date/Time value being displayed in [text125] and today's date? If so, use
Date() instead of [Date].

Regards

Jeff Boyce
Microsoft Office/Access MVP

My access database stopped calculating the age of people for some reason.
When it calculates, it shows the correct age (say 40) but in the format of
1940. I am using the difference of the last time the person took a test
vs.
their date of birth. the code being used is:

=DateDiff("yyyy",[text125],[Date])

even if I change yyyy to yy it still generates a 4 digit age. I have also
looked at the input mask and no matter what changes I make to it it does
not
change the output. the input mask is as follows:

99/99/0000;0;_

Any help would be appreciated as our frustration level is running high
with
this problem!!!!

 

[Klatuu]

A few notes that may help, James.

First, I think you misunderstand the Input Mask. It has no bearing on how
data is stored or displayed. It is only a tool to ensure data is entered
correctly; however, they are really more trouble than they are worth.

Also, your age calculation will not always get the correct age. For example
if someone is born June 1, 2000 and you check his age on May 25, 2007, it
will say he is 7, but he is not yet 7. His birthday is 6 days away. Here is
a function that will return the correct age based on the birthday.

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function

--
Dave Hargis, Microsoft Access MVP

I figured it out. After much playing with it. I removed the input mask and
changed it a general number but, still used the formula
=datediff("yyyy",[text125],[Date]) and everthing works great!!!! Thanks for
all the help!!!!

Jeff,
Thanks for the response. This is a database I took over and did not have
a hand in building. I hope this will clear up my question.... Dob is the
text125 you are seeing and date is the date of the test.

DOB : 01/01/2000

Age: ??? Trying to calc

Date (of test): 10/12/2006

trying to calc the age field based on date of test vs. dob. If I take the
test again on say 9/9/07 it will be another record. So record 1 should say I
am 6 and record 2 should say I am 7. etc..... if I do diff of
[text125],[date] I get a 4 digit age (see previous post). If I reverse it i
get a negitave but, correct age.

James

It all starts with the data. What does your underlying data look like?
What data type is being referenced in your [text125] control (by the way,
not very informative title -- will you remember what went in that control in
six months? Will someone else know?)?

Your expression includes "[Date]". This implies that you have a control
you've named "Date" (hence, the square brackets). FYI, Access treats "Date"
(and many other terms) as a reserved word, and may NOT be using the same
meaning you're thinking of.

Can I assume that you want to take the difference, in years, between a
Date/Time value being displayed in [text125] and today's date? If so, use
Date() instead of [Date].

Regards

Jeff Boyce
Microsoft Office/Access MVP



My access database stopped calculating the age of people for some reason.
When it calculates, it shows the correct age (say 40) but in the format of
1940. I am using the difference of the last time the person took a test
vs.
their date of birth. the code being used is:

=DateDiff("yyyy",[text125],[Date])

even if I change yyyy to yy it still generates a 4 digit age. I have also
looked at the input mask and no matter what changes I make to it it does
not
change the output. the input mask is as follows:

99/99/0000;0;_

Any help would be appreciated as our frustration level is running high
with
this problem!!!!

 

[Linq Adams via AccessMonster.com]

Do you understand that your formula will not always give you an accurate age?


datediff("yyyy",[text125],[Date])

yields the difference between the year of birth and the current year, so that
someone born in 1967 will be reported as being 40 even if they their
birthday is on 12/31! Might not matter much difference running the
calculation today (depending on your application) but could make a big
difference if you run the calculation on 1/1/08, when this same person would
be reported as being 41 years old! The following formula will give you an
accurate age, regardless of when the birthday falls.

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