Datediff help

I'm trying to calculate an age on a form using this datediff function.
Can anyone tell me why i just get #name? I created an 'age' field in
the table, and on the form in the control source for the 'age' field i
entered this where BDATE is the birthdate, and 17YRCADD is a test date
at which i need to determine the age. I know next to nothing about
vba so treat this like you are speaking to a total beginner.

=Abs(DateDiff("yyyy",[BDATE],[17YRCADD]())+(Format([BDATE],"mmdd")Format([17YRCADD](),"mmdd")))

Thanks for you help.

Rob S

Don't know whether it's the entire cause of the problem, but the parentheses
after [17YRCADD] are incorrect.

What is [17YRCADD], though: a value in the underlying table or query that
provides the recordset for the form, or a text box on the form?

I'm assuming that [BDATE] is both, in which case my normal advice is to
rename the text box on the form to distinguish it from the field in the
recordset: I rename all of my text box controls to have a prefix of txt, so
that assumgin [17YRCADD] is a text box, I'd have renamed it to txt17YRCADD,
and the text box that BDATE appears in would have been renamed to txtBDATE.

That having been done, I'd use

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

There's no reason to have an Age field in your table (in fact, technically
it's wrong to have it, as you should never have fields whose values are
derivable from other fields in the same row)


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


wrote in message
oups.com...
I'm trying to calculate an age on a form using this datediff function.
Can anyone tell me why i just get #name? I created an 'age' field in
the table, and on the form in the control source for the 'age' field i
entered this where BDATE is the birthdate, and 17YRCADD is a test date
at which i need to determine the age. I know next to nothing about
vba so treat this like you are speaking to a total beginner.


=Abs(DateDiff("yyyy",[BDATE],[17YRCADD]())+(Format([BDATE],"mmdd")Format([1
7YRCADD](),"mmdd")))

Thanks for you help.

Rob S

Thanks for your fast response. BDATE and 17YRCADD are both fields in
the underlying table and are placed as fields on the form with those
names as the control source. I'll change that now to your suggestion
and try again.

When you say I shouldn't have an age field in the table, instead I
should make an unbound control source on the form and use that
expression as the source?

Also, in the expression you have Me.txtBDATE. Whats the Me part? Is
that just saying its your suggested change? Thanks again

Rob S

Still no go. I removed the parentheses that you said were not needed,
and I renamed the text boxes on the form to txtBDATE and txt17YRCADD,
and used the expression that you supplied with and without the Me. part
since i have no idea what that does. I also tried using the table and
field name in the expression like this:

=Abs(DateDiff("yyyy",[age at test]![BDATE],[age at
test]![17YRCADD])+(Format([age at test]![BDATE],"mmdd")Format([age at
test]![17YRCADD],"mmdd")))

I still get #name. If i use the expression:
=Abs(DateDiff("yyyy",[BDATE],Now())+(Format([BDATE],"mmdd")Format(Now(),"mmdd")))

it gives me their current age. I just need it to use the 17YRCADD date
instead of now so i know their age as of that date. Any more ideas?

Is 17YRCADD a date/time field? Does it contain valid data?

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


wrote in message
ups.com...
Still no go. I removed the parentheses that you said were not needed,
and I renamed the text boxes on the form to txtBDATE and txt17YRCADD,
and used the expression that you supplied with and without the Me. part
since i have no idea what that does. I also tried using the table and
field name in the expression like this:

=Abs(DateDiff("yyyy",[age at test]![BDATE],[age at
test]![17YRCADD])+(Format([age at test]![BDATE],"mmdd")Format([age at
test]![17YRCADD],"mmdd")))

I still get #name. If i use the expression:
=Abs(DateDiff("yyyy",[BDATE],Now())+(Format([BDATE],"mmdd")Format(Now(),"mmdd")))

it gives me their current age. I just need it to use the 17YRCADD date
instead of now so i know their age as of that date. Any more ideas?

Me is a shorthand to refer to the specific class that's running. In the case
of a form, it's the current form.

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


wrote in message
oups.com...
Thanks for your fast response. BDATE and 17YRCADD are both fields in
the underlying table and are placed as fields on the form with those
names as the control source. I'll change that now to your suggestion
and try again.

When you say I shouldn't have an age field in the table, instead I
should make an unbound control source on the form and use that
expression as the source?

Also, in the expression you have Me.txtBDATE. Whats the Me part? Is
that just saying its your suggested change? Thanks again

Rob S

17YRCADD is a date/time field and is formatted just like BDAY
(mm/dd/yyyy). Both of these fields are in the same table, and this
table is just a small stand alone table with no relationships to any
other. The form is attached to the table, not a query.

Okay, let's review what you've done.

- your form has as its recordset either the table or a query that includes
both of the fields.
- you've got text boxes on the form that are bound to the two fields in
question.
- you've renamed the text boxes so that they're txtBDAY and txt17YRCADD
- you've got an unbound text box (I'll call it txtAge)
- the control source for txtAge is
=Abs(DateDiff("yyyy",Me.txtBDATE,Me.txt17YRCADD)+( Format(Me.txtBDATE,"mmdd")
Format(Me.txt17YRCADD,"mmdd"))) (all on one line, with the = sign in front)
- you're getting #name in txtAge, but no error message.
- you've double checked that you haven't mistyped anything in the equation
above

Are all of the statements above correct? If not, please indicate which
aren't.




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


wrote in message
oups.com...
17YRCADD is a date/time field and is formatted just like BDAY
(mm/dd/yyyy). Both of these fields are in the same table, and this
table is just a small stand alone table with no relationships to any
other. The form is attached to the table, not a query.

All of the above information is completely correct. I've coppied the
expression as i have it in the control source:

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

I get no error messages at all, just the #name problem instead of an
age.

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?