Datediff help

  • Thread starter Thread starter xerxov
  • Start date Start date
X

xerxov

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

(no e-mails, please!)


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?
 
Me is a shorthand to refer to the specific class that's running. In the case
of a form, it's the current form.
 
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.
 
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?
 
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)+(Format(Me.txtBDATE,"mmdd")>Format(Me.txt17YRCADD,"mmdd"))))
 
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.
 
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.
 
Back
Top