Calculating age

[Paolo]

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).

I would like that when users open the form, a message should alert if
that person is over age 96 (yes I do have old clients...). If under
age 96, I would not like any message.

The match should therefore be done between the date stored in the DOB
field and the current date.

Is there a code to do this and where should it go.

[Allen Browne]

Copy the code from this link into a standard module:
http://members.iinet.net.au/~allenbrowne/func-08.html

You can then put a text box on your form, with this its ContorlSource:
=IIf(Age([DOB]) >= 96, "Geriatric warning", Null)

The function allows you to specify a death date if appropriate.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com...

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).

I would like that when users open the form, a message should alert if
that person is over age 96 (yes I do have old clients...). If under
age 96, I would not like any message.

The match should therefore be done between the date stored in the DOB
field and the current date.

Is there a code to do this and where should it go.

[Mike Storr]

If it is only to happen when the form happens, then in either it's On Open
or On Load events add

If DateDiff("yyyy" ,Me!DOBField,Da te) >= 96 Then
MsgBox ("Message Here", ButtonStyle, Title)
end if

"Paolo" <jp***@tin.it > wrote in message
news:9f******** *************** ***@posting.goo gle.com...

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).

I would like that when users open the form, a message should alert if
that person is over age 96 (yes I do have old clients...). If under
age 96, I would not like any message.

The match should therefore be done between the date stored in the DOB
field and the current date.

Is there a code to do this and where should it go.

[John W. Vinson]

On 30 Jan 2004 06:53:03 -0800, jp***@tin.it (Paolo) wrote:

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).
Well... actually, it stores the date of birth as a Double Float
number, a count of days and fractions of a day since midnight,
December 30, 1899. The display format is irrelevant to the problem.
I would like that when users open the form, a message should alert if
that person is over age 96 (yes I do have old clients...). If under
age 96, I would not like any message.

The match should therefore be done between the date stored in the DOB
field and the current date.

Is there a code to do this and where should it go.

I'd suggest code in the Form's Current event to bring up a message
whenever someone either opens the form, or navigates to an the record
of such an Esteemed Elder (note: in Chinese culture, someone who
reaches the age of 96 is considered an "honarary centenarian"). Try
code like

Private Sub Form_Current()
Dim iAge As Integer
iAge = DateDiff("yyyy" , [DOB], Date())
' Correct for people whose birthday has not yet arrived
If DateSerial(Year (Date()), Month([DOB]), Day([DOB])) > Date() Then
iAge = iAge - 1
End If
If iAge >= 96 Then
Msgbox "This person is " & iAge & " years old, handle with respect!"
End If
End Sub

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

[Lyle Fairfield]

John W. Vinson <jvinson@STOP_S PAM.WysardOfInf o.com> wrote in
news:a2******** *************** *********@4ax.c om:

On 30 Jan 2004 06:53:03 -0800, jp***@tin.it (Paolo) wrote:

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).

Well... actually, it stores the date of birth as a Double Float
number

It stores it in 8 bytes.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[CDB]

Being two 4-byte integers, IIRC.

Clive
"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

John W. Vinson <jvinson@STOP_S PAM.WysardOfInf o.com> wrote in
news:a2******** *************** *********@4ax.c om:

On 30 Jan 2004 06:53:03 -0800, jp***@tin.it (Paolo) wrote:

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).

Well... actually, it stores the date of birth as a Double Float
number

It stores it in 8 bytes.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Paolo]

Thanks, this is exactly what I am looking for. There is still one
debugging problem on the line: iAge = DateDiff("yyyy" , [DOB], Date())

Any idea?

John W. Vinson <jvinson@STOP_S PAM.WysardOfInf o.com> wrote in message news:<a2******* *************** **********@4ax. com>...

On 30 Jan 2004 06:53:03 -0800, jp***@tin.it (Paolo) wrote:

I have a form on which I have a field named DOB which stores the
client's date of birth (mm/dd/yyyy).

Well... actually, it stores the date of birth as a Double Float
number, a count of days and fractions of a day since midnight,
December 30, 1899. The display format is irrelevant to the problem.

I would like that when users open the form, a message should alert if
that person is over age 96 (yes I do have old clients...). If under
age 96, I would not like any message.

The match should therefore be done between the date stored in the DOB
field and the current date.

Is there a code to do this and where should it go.

I'd suggest code in the Form's Current event to bring up a message
whenever someone either opens the form, or navigates to an the record
of such an Esteemed Elder (note: in Chinese culture, someone who
reaches the age of 96 is considered an "honarary centenarian"). Try
code like

Private Sub Form_Current()
Dim iAge As Integer
iAge = DateDiff("yyyy" , [DOB], Date())
' Correct for people whose birthday has not yet arrived
If DateSerial(Year (Date()), Month([DOB]), Day([DOB])) > Date() Then
iAge = iAge - 1
End If
If iAge >= 96 Then
Msgbox "This person is " & iAge & " years old, handle with respect!"
End If
End Sub

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public