Updating an Age field using the built in date 1

[Viv1]

Hello,

I have a field called Age which is calculated when a date of birth is entered using the code:
Me!Age = DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), "mmdd") < Format([DOB], "mmdd"))

The problem is that this field does not update automatically when the date changes. Is there a way to do this and at the same time reference a Call Function?

The Call function sees the Age of the person and calculates how much they should be paying so it is important the Age change triggers the call function.

Any ideas appreciated.

Thanks

 

[KenReay]

Hi

This is a perfect explanation of why you should not store the Age. You should store the DOB and use a Query to include an age column, calculated via a suitable function base on todays date (or another suitable date ie as in Age next week, or Age on 1st Jan next year etc)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[konacq]

Viv1:

You can recalculate the age each time the form opens, as in:

Private Sub Form_Open(Cancel As Integer)
Me!Age = DateDiff("yyyy", [DOB], Now()) + Int(Format(Now(), "mmdd") < Format([DOB], "mmdd"))
If Me!Age > 40 Then
Call My_Function
End If
End Sub

The problem is, you have to open the form - the database won't just do it.

Alternatively, if you have the date of birth in your record, you can query on it.

Konacq

 

[Russ1005]

I am new to Access. I have a field on a form called csr that I want to populate with the user network login and display the value on the screen when as soon as the form displays.

Here is the function I used but the user ID returned from the function does not display on the screen. the fOSUserName function returns the ID properly when tested in the immediate window. Any help would be appreciated.

Private Sub csr_BeforeUpdate(Cancel As Integer)
Me!csr = fOSUserName
End Sub


Russ1005

 

[TheAceMan1]

How are ya Russ1005 . . . . .

You'll have a better chance of getting answers if you start a new post! Here your question is burried! ;-)

See Ya! . . . . . .

 

[KenReay]

Hi

Two points

1 as aceman says, start a new post

2 you have code in wrong event

Try in form open or form load event


Me!csr = fOSUserName()
DoEvents





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK