1. Age (access 97)

Hello everyone,
I've got a simple one (?) I've got two fields, Birthdate (Their birthdate) and StDate (The date they enter our school). I want to calculate their age in years at the time they enroll (StDate) as another field (EnrollAge). DateDiff was no good (simply subtracted the years) and Age() and AgeMonths() calculate using Now, which keeps giving their current age, instead of the locked in age that they were when they signed up. I'm hoping for a function, as my programming skill are rudimentary at best.

Any help is appreciated,
Samuelww

2. Re: Age (access 97)

Stick this function in a module, and see if it works. It probably could be compressed into a singe IIF statement, but it is easier to read the way I wrote it.
<pre>Public Function GetAgeAt(BirthDate as date, OtherDate as date) As Integer
Dim intAge As Integer

intAge = DateDiff("yyyy", BirthDate, OtherDate)
If DateAdd("yyyy", intAge, BirthDate) > OtherDate Then
GetAgeAt = intAge - 1
Else
GetAgeAt = intAge
End If
End Function
</pre>

3. Re: Age (access 97)

Mark,
Thanks for your quick reply. I'll try it and let you know.
Thanks,
Sam

4. Re: Age (access 97)

I just recently did it calculating the months and dividing by 12. Then setting the number to Fixed and the decimal to 1. I like seeing if a child is 14.1 or 14.9. Then I can easily tell their approximate birthday.

=(DateDiff("m",[Date_of_Birth],Now()))/12

5. Re: Age (access 97)

>> =(DateDiff("m",[Date_of_Birth],Now()))/12 <<

There is a flaw in this equation. The nature of how DateDiff works is that it counts the number of different months between 2 dates, and not the number of 30-day intervals, etc. For example, take this calcuation of the difference between 2 consecutive dates:
'
' first look at datediff for 1-day interval 12/30/01 to 12/31/01
DateDiff("m",#12/30/01#, #12/31/01#) returns 0
DateDiff("yyyy",#12/30/01#, #12/31/01#) returns 0
'
' now look at another 1-day interval: 12/31/01 to 1/1/02
DateDiff("m",#12/31/01#, #1/1/02#) returns 1
DateDiff("yyyy",#12/31/01#, #1/1/02#) returns 1

6. Re: Age (access 97)

To Mark and Sandy,
Well, actually I thought of something that is similar to what Sandy suggested. Instead of using months I used days and divided by 365.25. Seems to work pretty good!
To Mark-most of the suggestions in this group seem to be in code, which I have so far successfully avoided learning. However, I'm getting the distinct impression I may need to learn at least a little if I want to do much more customization. Two questions: is there a good source for third party add-ons for Access 97 and is there a good beginner's book for writing code for Access, like "Access VBA for Dummies"?

7. Re: Age (access 97)

In all honesty, if you want to develop in Access you will have to learn VBA. The book I recommend (and the one I have) is "Beginning Access97 VBA" by Sussman and Smith; althought I'm sure the have updated versions.

To use the function I wrote, you merely have to paste it into a module. You can then call it from anywhere. In a query, the expression would be something like this:

EnrollmentAge: GetAgeAt(birthdate, enrollmentDate)

In a report or a form, you can set the control source of a form to be:
=GetAgeAt(birthdate, enrollmentDate)

You can get the current age by using: GetAgeAt(Birthdate)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•