Convert date to age function

[TammyT]

Okay, forgive me, but I'm missing something obvious!

I have a field on a form to capture a birthdate (field name = Ch1_DOB). I need to convert that date to the child's age. I found a function, but I'm just not getting how to use it! I know my field name should go in there somewhere, but I'm not sure where, & for the life of me I can't seem to get the function to run!

Sorry - it's been almost a year since I've been called on to do Access...bear with me! I'm using Access 2002.

Here is the function code as taken from the FAQ:

Function Age (Birthdate as Date) as Integer
Age = DateDiff("YYYY", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate)))
End Function

Help?

Thanks!

 

[JerryKlmns]

Form bound to a query?

SELECT *, Age(myBirthDateField)
FROM ...

 

[TammyT]

No - it's bound to a table; once the age is figured, the value will go into the Age field in the table

 

[JerryKlmns]

So, you are storing a calculated value in a table? Ok then. Are you going to update it everyday?

Imaging a query about childs of age 5, running monthly. Records added 2 months ago shows wrong values!

 

[Golom]

Records added 2 months ago shows wrong values!

Records added yesterday could show wrong values!

To have the ages automatically computed you would need to define the default value for the field as [red]Age([BDate])[/red] where [BDate] is the name of the BirthDate field. Access doesn't support that so you would need to run this

UPDATE myTable SET TheAge = Age([BDate])

EVERY DAY.

 

[TammyT]

No - I only want the value calculated & stored once. It's for a file review tool - I need to capture the child's age at the time of review, & never change it.

Suggestions?

Also - there may be up to 10 children on a given review, so I'll need to either write it or run it up to 10 times.

 

[Golom]

OK. Then you need to capture the review date as well. The Age function that you have will report the age as of the current date (i.e. whenever it is run.) You might want an Age Function more like

Function Age (Birthdate as Date, ReviewDate As Date) as Integer
Age = DateDiff("YYYY", Birthdate, ReviewDate) + _
(ReviewDate  < DateSerial(Year(ReviewDate), Month(Birthdate), Day(Birthdate)))
End Function

and use

UPDATE myTable SET TheAge = Age([BDate],[Enter Review Date])

 

[TammyT]

Okay - bear with me - like I said, it's been a while!

1) Where you have Birthdate, am I going to use my field name?
2) Same for ReviewDate - is that my field name?
3) Update - they are filling in a form, & I want to calculate the age on the form, then store it in the table; or is that backwards? Should I store it, then pull it right in for display?

Sorry if I'm being difficult!

Thanks.

 

[Golom]

1) Where you have Birthdate, am I going to use my field name? [blue]Yes[/blue]

2) Same for ReviewDate - is that my field name?
[blue]Maybe. It will presumably be constant for every record and storing redundant information is usually not recommended. You might want to store it in another table and then.

UPDATE myTable A, LastReviewTable L 
SET TheAge = Age(A.[BDate],B.LastReviewDate)

Where "LastReviewTable" has only one record containing the date of the last review.[/blue]

3) Update - they are filling in a form, & I want to calculate the age on the form, then store it in the table; or is that backwards? Should I store it, then pull it right in for display?
[blue]You can certainly do that

Dim SQL As String
SQL = "INSERT INTO myTable (Name, BDate, Age) " & _
      "VALUES('" & varName & "',#" & varBDate & "#," & _
              Age(varBDate, varLastReviewDate) & ")"
CurrentDb.Execute SQL

[/blue]

 

[TammyT]

Wait, I'm going in circles.

The code you gave me for the function is the same as what I have, except you added ReviewDate As Date at the top, right?

So, all I really want to do, then, is call up the function when, on the form, the Birthdate field is updated. I'm thinking I just need to run an Event Procedure on the After Update property of the b-date field that calculates the age, & puts the results in the Age field on the form, which then automatically writes to the table.

I feel like I'm asking something simple, but I'm getting confused in the complication of the answers.

 

[Golom]

Sort of.

The reason for capturing a Review Date rather than just using the Date() (i.e. today) function is that someone looking at the table may reasonably ask "The Age on What date?"

Using the Date() function, the only answer is "The date the update was run ... whenever that was." Because that information isn't in the database anywhere it just depends on someone remembering when the update ran.

Personally ... I don't remember last Wednesday all that well ... and certainly not the last time I ran a particular query.

 

[TammyT]

HAHA!! Thanks - I was afraid you might start getting upset at my denseness!!!

They are only ever going to enter on the form once; if I create an Edit form copy of it, there won't be any updating - just post the age as written to the table.

So, I should put the function on the code page for the form, not on a module, right?

Then, once the function is written, I want to apply it to the "after update" event on the DOB box, right? That's where I'm stuck - calling up the function.

Last Wednesday? Did I miss the weekend? !!

 

[Golom]

Although you can compute and enter the Age as you insert records, there remains the question "When was the record entered?" You are not (I gather) capturing that so the meaning of the Age field is somewhat problematic. A given Age may be the result of any date in a 365-day period. It may not be important for you to know a specific day.

There was a weekend???

 

[TammyT]

Hey - I solved it! I went into the After_Update event for the DOB field, put the code there, & it works! This way - it only runs when DOB is updated, & DOB won't ever be updated once it's entered, unless it was wrong, in which case refiguring is okay, blah blah blah!!

Thanks.

Did you see my post from today?

 

[Golom]

You mean this one? thread181-1125803

Looks like PHV and LauryBurr took care of you just fine.

 

[TammyT]

No - that one is old.

This is the runtime 2247 error one.

Thanks!