Storing a calculated age in a table

[Jonathan]

I have a table Contacts, with the fields 'name', 'birthdate' and 'age'. I am
able to calculate an age based on the birthday in a form by using the command:

= DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") <
Format([Birthdate], "mmdd"))

in the control source in the properties of the 'age' field on that form.
Buu when I flip back to the table, the calculation is not there. Is it
possible to have the calculated age stored in a field of a table?
Ultimately, I would like to have a report sorted by age, or filtered by age.
I am using Access 2003 on Windows XP Pro SP2

 

[Allen Browne]

There is NO way this kind of data should be stored in your table. A few
hundred people, and you have wrong data every day.

Create a query into this table.
Type the expression into the Field row in the query.
You can sort by this calculated field, or enter criteria beneath it.

Use the query as the RecordSource for your report.

 

[John Vinson]

Ultimately, I would like to have a report sorted by age, or filtered by age.

So base the Report on a Query containing that calculated age. There's
no need to put it into a Table to do so, and in fact it's a very bad
idea.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Frank J]

My situation is someone different. I calculate a person's age at the time a
certain action was taken using:
=datediff("yyyy",[dob],[dateofdecision])+int(format([dateofdecision],"mmdd")<format([dob],"mmdd")).
This calculation takes place in my record update form. The age listed never
changes. I want the calculated result to populate the field "age" in my
table. So, my problem is identical Jonathan's except that my age calculation
is not dynamic. I only need the age information when generating reports for
the most part and they are often using a Query as the data source. Any
suggestions for this novice?

There is NO way this kind of data should be stored in your table. A few
hundred people, and you have wrong data every day.

Create a query into this table.
Type the expression into the Field row in the query.
You can sort by this calculated field, or enter criteria beneath it.

Use the query as the RecordSource for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table Contacts, with the fields 'name', 'birthdate' and 'age'. I
am
able to calculate an age based on the birthday in a form by using the
command:

= DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") <
Format([Birthdate], "mmdd"))

in the control source in the properties of the 'age' field on that form.
Buu when I flip back to the table, the calculation is not there. Is it
possible to have the calculated age stored in a field of a table?
Ultimately, I would like to have a report sorted by age, or filtered by
age.
I am using Access 2003 on Windows XP Pro SP2

 

[John Vinson]

On Fri, 17 Dec 2004 10:23:05 -0800, "Frank J" <Frank

My situation is someone different. I calculate a person's age at the time a
certain action was taken using:
=datediff("yyyy",[dob],[dateofdecision])+int(format([dateofdecision],"mmdd")<format([dob],"mmdd")).
This calculation takes place in my record update form. The age listed never
changes. I want the calculated result to populate the field "age" in my
table. So, my problem is identical Jonathan's except that my age calculation
is not dynamic. I only need the age information when generating reports for
the most part and they are often using a Query as the data source. Any
suggestions for this novice?

Put code like the following in the AfterUpdate event of the
[DateOfDecision] control. View the control's Properties, click the ...
icon by the AfterUpdate event, and choose the Code Builder option;
Access will give you the Sub and End Sub lines. Assuming that you have
another textbox named Age on the form the code would be:

Private Sub DateOfDecision_AfterUpdate()
Me![Age] = datediff("yyyy", [dob], _

[dateofdecision])+int(format([dateofdecision],"mmdd")<format([dob],"mmdd"))
End Sub


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps