Date Function

[HL]

Please help with a function

I have a field where I can enter the D.O.B details on a form.

After I exit the field, it will then automatically update an unbound field with the age of the person.

But the function must work out automatically the age of the person when increases by a year when it is there birthday.

Thanks for your help......

 

[donbettis]

This may not be what you are looking for... This will show the age but, it doesn't update until you leave the record and come back...

However This is what I use:

I place the below in the OnCurrent event of my form...

Private Sub Form_Current()
Dim SerToday As Date
Dim SerBirthday As Date
Dim SerThisBirthday As Date
Dim SerNextBirthday As Date
Dim SerLastBirthday As Date
SerToday = Date
SerBirthday = DateSerial(Year(Me!DOB), Month(Me!DOB), Day(Me!DOB))
SerThisBirthday = DateSerial(Year(Now()), Month(Me!DOB), Day(Me!DOB))
If SerThisBirthday < SerToday Then
SerNextBirthday = DateSerial(Year(Now()) + 1, Month(Me!DOB), Day(Me!DOB))
SerLastBirthday = DateSerial(Year(Now()), Month(Me!DOB), Day(Me!DOB))
Else
SerNextBirthday = DateSerial(Year(Now()), Month(Me!DOB), Day(Me!DOB))
SerLastBirthday = DateSerial(Year(Now()) - 1, Month(Me!DOB), Day(Me!DOB))
End If
If Abs(SerToday - SerLastBirthday) > Abs(SerToday - SerNextBirthday) Then
Me!Age = Year(SerNextBirthday) - Year(Me!DOB)
Else
Me!Age = Year(SerLastBirthday) - Year(Me!DOB)
End If
End Sub

Where Me!DOB is the field that stores the person's date of birth, and Me!Age is an unbound textbox on the form to show the Age.


Hopefully This Will Help

Don

Edited because of typo & I added something

[This message has been edited by donbettis (edited 01-02-2002).]

 

[R. Hicks]

Hmmm .... I think that's a bit "overkill".

Copy this Function to a new module:

Public Function fGetAge(DOB As Variant) As Integer
If Not IsDate(DOB) Then Exit Function
fGetAge = DateDiff("yyyy", DOB, Date) + (Date < DateSerial(Year(Date), Month(DOB), Day(DOB)))
End Function

Now in the unbound txtbox use the following as the Control Source for the txtbox:

=fGetAge([DOB_txtboxName])
("DOB_txtboxName" should be the name of the textbox that contains the DOB info)

As soon as the DOB info is typed in ... the age will appear.

HTH
RDH

[This message has been edited by R. Hicks (edited 01-02-2002).]

 

[donbettis]

R. Hicks

I have been accused of doing things the hard way

However in my defense... It was the only way I knew to achieve what he needed...

We all learn new ways of doing things...As I may have just learned something new...

And I try help people where I can...

Don

 

[R. Hicks]

Don .... don't take my reply wrong. I was not "Criticizing" your method. I only meant that it was much more than needed. The "rule of thumb" in programming is "simpler is better".

I have learned most of what I know about Access and VBA by answering questions on forums such as this (and I have answered thousands). This a very good way to learn as you will encounter scenarios that you may never encounter normally ..... so "continue to help" people.

RDH

 

[donbettis]

R. Hicks

Thanks and I will always help people if I can… My methods are not always the best ones however, they are the only ones I know…

I am still learning a lot about Access and VBA by asking & answering questions…

Sometimes I know how to do what peoples are asking… I just can’t always put it in words so they can understand it…

Doing is easier then explaining sometimes…

Don