Datediff help

Yes, that's the problem.

If 17YRCADD doesn't have a value in it, what do you want to do?

To have it use today's date instead, you could use:

=Abs(DateDiff("yyyy",Me.txtBDATE,Nz(Me.txt17YRCADD ,
Date()))+(Format(Me.txtBDATE,"mmdd")Format(Nz(Me. txt17YRCADD,
Date()),"mmdd")))

To have it report a problem, you could use:

=IIf(IsNull(Me.txt17YRCADD), "Problem",
Abs(DateDiff("yyyy",Me.txtBDATE,Me.txt17YRCADD)+(F ormat(Me.txtBDATE,"mmdd")Format(Me.txt17YRCADD,"m mdd"))))



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
oups.com...
As a test, i tried the expression that does work using Now and 17YRCADD
as the date instead of BDATE. I get the same #name error, so the
problem seems to be with the 17YRCADD field. I looked through the data
in that field and the only difference I can see between that and BDATE
is that all of the records have a date in BDATE but not all have a
17YRCADD date. I didn't think this would be a problem in the records
where there was an entry in 17YRCADD. Could this be the problem?

I don't understand why it doesnt work on any of the records when only
some of the records don't contain both dates. How is Access dealing
with this that it wants dates in all the fields before it will run that
expression on any of the records? I have a number of Access books and
none of them have been any help in understanding this issue.

I tried the expression to have it report a problem. it gave me the
same #name error in all of the records. BDAY is birthday. 17YRCADD is
a test that was given at some point in time, between the ages of 16 and
20. I am trying to figure out the age at the time the test was given,
not as of today so using todays date wouldnt help.

I can't believe that this database is incapable of performing a
calculation between 2 date fields unless every single record in the
table has dates in both fields. No other database that I have ever
seen has such an astounding limitation. Are you telling me that unless
BDATE and 17YRCADD both have a date in every record, Access can not
perform a calculation to determine the age?

No, I'm not telling you that.

Either you've made a mistake typing something, or there's something else
about your data that you haven't noticed.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
oups.com...
I tried the expression to have it report a problem. it gave me the
same #name error in all of the records. BDAY is birthday. 17YRCADD is
a test that was given at some point in time, between the ages of 16 and
20. I am trying to figure out the age at the time the test was given,
not as of today so using todays date wouldnt help.

I can't believe that this database is incapable of performing a
calculation between 2 date fields unless every single record in the
table has dates in both fields. No other database that I have ever
seen has such an astounding limitation. Are you telling me that unless
BDATE and 17YRCADD both have a date in every record, Access can not
perform a calculation to determine the age?

Given that the expression is typed correctly, both fields are set as
date/time fields, there is no erronious data in those fields, the
correct table is attached to the form and the text boxes on the form
are named differently that the fields in the table, and the expression
is in the control souce of an unbound text box, do you have any other
ideas as to why all of the expressions that you gave me won't give me
an age for those records that have both a BDATE and 17YRCADD date? I
am at a complete loss.

Sorry, so am I.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
oups.com...
Given that the expression is typed correctly, both fields are set as
date/time fields, there is no erronious data in those fields, the
correct table is attached to the form and the text boxes on the form
are named differently that the fields in the table, and the expression
is in the control souce of an unbound text box, do you have any other
ideas as to why all of the expressions that you gave me won't give me
an age for those records that have both a BDATE and 17YRCADD date? I
am at a complete loss.