But what I need the age result to be is based on the month and year of
birth
not so much the day. I need to figure a players league age based off a
cut
off of April 30 of the coming year. So if a player enters our program
today
and is 7 years old now but born in April of 98 his league age would be 8
because he will reach the age of 8 by the end of April. If his DOB is May
of
98 his league age is 7 because he was still 7 in the month of April. The
expression I put in place is working that way. It's not giving me the
exact
Years Months Days, but I don't need it to. I just need the Year age the
player would be by the end of the month of April. Is there another way to
calculate this. Remember my knowledge is very limited. This is the first
time I've ever worked with Access. Thanks for your help.
Brendan Reynolds said:
Unfortunately, that expression is not accurate. For example, I was born
on
the 22nd of May. That expression would return an incorrect result for my
birth date on the 1st of May to the 21st of May, inclusive.
--
Brendan Reynolds
Cris said:
It's bound directly to the table. And it wouldn't work the way I wrote
the
expression above. I had to develop another text box to hold my default
date
and convert the date difference from both dates into month's and divide
by
12:
=Int(DateDiff("m",[DOB],[DetermDate])/12). Now it works perfectly. I
can
change my default determination date whenever I need to or lock it in
place
so that it always calculates from that specific date. I'm not sure why
the
other way wouldn't work in my form. It kept giving me the error.
:
Ah, thanks Doug. I actually copied and pasted the expression,
including
the
original date format, Access automatically changed it to match my
regional
settings.
Cris, I still think the problem may lie in the record source - is the
form
bound directly to the table, or is it bound to a query or SQL
statement
based on the table? If a query or SQL statement, are you *sure* that
query
or SQL statement includes the DOB field in the SELECT clause?
--
Brendan Reynolds
message
I think he's talking that you used dd/mm/yyyy in your example, while
his
original example was mm/dd/yyyy.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
I'm not sure what you mean by 'don't have my date set up'.
--
Brendan Reynolds
Yes It does. and putting a $ instead of # in my post was a typo
on
my
part
in the expression itself it does have the # . I don't have my
date
set
up as
you do down below though, Could that be the problem?
:
I copied and pasted your expression, changing only the name of
the
DOB
field, to match the name of a field in my test table ...
=DateDiff("yyyy",[BirthDate],#30/04/2006#)+(Format([BirthDate],"mmdd")>Format(#30/04/2006#,"mmdd"))
.... and could not reproduce the error. So there doesn't seem to
be
anything
wrong in the expression itself. This leads me to wonder if the
error
might
be in the record source of the form - does the record source of
the
form
include the DOB field?
--
Brendan Reynolds
I'm trying to calculate DOB based off the specific date
4/30/2006.
I've
read
all the posts and have entered the following expression in the
control
souce
of an unbound text box in my form:
=DateDiff("yyyy",[DOB],$4/30/2006#)+(Format([DOB],"mmdd")>Format(#4/30/2006#,"mmdd")).
[DOB] is a field in my table.
It is not doing anything. When I transfer back to the data
entry
view of
my form #Name? appears in the box that should contain/display
my
calculation.
What am I doing wrong? I'm getting very frustrated. Any help
anyone
could
give would be great. Is there another way to caculated the
age.
