Age Update

[uksoldier]

I want my AGE field in my database to update once my data input clerks insert the Date of Birth.

Think I have to run an update query but could do with some advice on how to create one.

I know I have to run it for the update to happen but am suffering trying to create one

 

[Mile-O]

If you are storing the Date Of Birth in a table then there really is no need for you to store the age too.

If you run a typical query and want to a field to show everyone's age then on the query builder add an expression as the title of a field:

Age: CalculateAge([Date Of Birth],Date())

That field assumes that [Date Of Birth] is the name of your DOB field.


And now, all that needs to be done is to put this piece of code into a module and the query will calculate a person's age at run-time.

Function CalculateAge(ByVal dteBirth As Date, ByVal dteToday As Date) As Integer

    On Error GoTo Err_Age
   
    ' ensure that date range is not negative
    If dteBirth > dteToday Then
        MsgBox "Error: Date range returns negative value."
        Exit Function ' function will not continue
    End If

    If Month(dteToday) < Month(dteBirth) Or (Month(dteToday) = Month(dteBirth) _
        And Day(dteToday) < Day(dteBirth)) Then
        Age = Year(dteToday) - Year(dteBirth) - 1
    Else
        Age = Year(dteToday) - Year(dteBirth)
    End If
    
Exit_Age:
    Exit Function

Err_Age:
    MsgBox Err.Number & Err.Description
    Resume Exit_Age
    
End Function

 

[uksoldier]

AGE

Well this is a medical database and the age is required. All I want it to do is to add the actual age to the table when adding the DOB.

 

[Mile-O]

Well, if you want the age to be put in the table automatically after the input of the DOB then

on the form (I'm guessing you are doing this with a form):

The control where the user inputs the DOB, on its AfterUpdate event you could put this line:

Me.txtAge = CalculateAge(txtDOB,Date())

(where txtAge is a textbox with its controlsource set to the Age fieldof your table and has its Visible property set to False)

and still put the function above in a module.


Or am I totally missing the point?

 

[uksoldier]

So.....

Sorry... but its sunday and Im tired yea u hit the point straight on thehead... however having done that in the form

and cutting and pasting the module, saving everything running the form after I tab into the age box it doesnt update... do I need to amend any of the module ? the field name for the Date of Birth is DOB.

Thanx a lot for your patience

 

[Mile-O]

No sorry, my fault - I changed the function to avoid confusion but forget to change it all.

Copy an paste this function:

Function CalculateAge(ByVal dteBirth As Date, ByVal dteToday As Date) As Integer

On Error GoTo Err_Age

' ensure that date range is not negative
If dteBirth > dteToday Then
MsgBox "Error: Date range returns negative value."
Exit Function ' function will not continue
End If

If Month(dteToday) < Month(dteBirth) Or (Month(dteToday) = Month(dteBirth) _
And Day(dteToday) < Day(dteBirth)) Then
CalculateAge = Year(dteToday) - Year(dteBirth) - 1
Else
CalculateAge = Year(dteToday) - Year(dteBirth)
End If

Exit_Age:
Exit Function

Err_Age:
MsgBox Err.Number & Err.Description
Resume Exit_Age

End Function

 

[uksoldier]

Date Format

Sorry... doesnt work I am in the UK ! and am entering dates as dd/mm/yyyy does that make a difference ?

 

[Mile-O]

Did you use this line verbatim?

Me.txtAge = CalculateAge(txtDOB,Date())

If so then change the two objects txtAge and txtDOB to the names that you have given them on your form.



The dates shouldn't interfere here; I'm in the UK too.

 

[uksoldier]

Check

Are you able to check the form and module... ? Ive attempted to attach the test file but its not valid ? paulstretton@madasafish.com is my email addy if you could !

I know this isnt orthadox... but hey I have to ask