Software

Ask something
Discussion Locked

Helps with Access

0 helpful
25 replies

by cmorus

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

A couple of years ago I created a field with the following formula:

=INT(DATEDIFF)("y;[DATEOFBIRTH],Date())/365)

to determine the age. Recently we started getting an error message: #NAME?

I've re-chosen the field names, but am still getting the error. I no longer do much with Access and cannot figure out the problem. Any ideas? Thanks so much.

25 replies

by rajaragavan k

Can You please give some more details like is the DATEOFBIRTH is a field in any table and what is the data type of that like this.

by rsv

HI
Try this....

date1="8/20/1970"
date2="8/20/2000"

=int(datediff("y",date1,date2)/365)

its returns 30.

Good Luck...:)

by cmorus

This formula was designed to calculate a student's age based on today's date. Thanks.

by glen_mcleod

RSV got it right, you've inversed one of your parentheses and you forgot the closing quote for the 'y'.

Glen

by dklippert

You've lost a " mark and gained a ). Try this:
=INT(DATEDIFF("y", [DATEOFBIRTH], Date())/365)

by donq

=Int(DateDiff(?y?, [DATEOFBIRTH], Date())/365) & ? years ? & DatePart(?m?, Date())-DatePart(?m?, [DATEOFBIRTH]) & ? months.?

The above returns ?64 years 6 months.? when [DATEOFBIRTH] is a Date/Time field formatted Short Date.

by cmorus

Thanks for all your ideas. I checked the formula and my typing/handwriting was wrong. The formula in the Age fields was:

=INT(DATEDIFF("y", [DATEOFBIRTH], Date())/365)

exactly as Doug indicated.

So, what does "#NAME?" mean in the Age Box (where the formula is)? I thought it meant there was something wrong with the name of the field, but I chose it from the list of fields and have even tried changing it.

I also get an error message: "Undefined function 'Date' in Expression" when Itry to run a report that is supposed to sort on the Age field.

Oh, by the way, the field type is Short Date.

Thanks!

by dklippert

#NAME does mean that the name is not recognized, that and the undefined function statement make me think that there is a typo in the formula.
Try putting the expression together in pieces and then concatenating the results.

by cmorus

Doug, not sure what putting it together in pieces means.

I'm sure the formula is typed in the field exactly the way you did it. Actually, I even re-named the field to be birthday and re-chose it from the field list.

Can you be more specific about doing it in pieces? Thanks.

by peyison

#Name? can also mean that one of the functions is not recognized. Since is sounds like the field name is correct, and you're having problems with the Date() function in another place, I'd say for some reason Access cannot recognize the Date() function. Try hardcoding a date in place of Date().

If that still doesn't work, try:

=Int((#8/11/01#-[DATEOFBIRTH])/365)

This should work, so then replace the hard coded date with Date().

If the problem is with either of the functions Date() or DateDiff(), you might try reinstalling Access.

Hope this helps.

by raffi_

This is a KNOWN bug in Access. Do this:

Open your DB.
Press Control G (opens the VB window)
Go to Tools>References.
UNCHECK any items that indicate or start with "missing . . .".
Click OK.
Close the VB window.
Exit the DB
Restart the DB.
Regards
Raffi_

by cmorus

Wow! What great ideas. I'll be there next week and give it a try. I'll keep you posted. Thanks for all the help!

by bobf

You are overly complicating the formula. Try:
Me![txtAge] = DateDiff("yyyy", [DATEOFBIRTH], Date). Put this in the form Current event and be sure to create the text control txtAge.
Using "yyyy" eliminates the need for the Int function and dividingDate() by 365. The () after Date aren't needed.
Also make sure DATEOFBIRTH field on the form is bound to the DATEOFBIRTH field in the underlying query or table.

This discussion is closed to new replies.