How to save calculated data from an unbound text box into a table

[Bonnie]

I have a unbound text box that calculates the age of a person. I need this
age to be saved in a text box (this box only needs the age of the person at
date of admission).

I have used the following in my countrol source, but I don't know how to
tranfer this info into a field named age at admission. I also need to keep
this field as upto date as I may need to check current age sometimes to
determin a move.

Please can some one help as I am trying to create this database and due to
this small teething problem I am on a standstill.

 

[Wayne-I-M]

Hi Bonnie

Of course you could save the results of a form's text box into a field
me.XXX=DateDiff("yyyy",[DateofBirthField],Date())-IIf(Format([DateofBirthField],"mmdd")>Format(Date(),"mmdd"),1,0)
but what would be the point.

You can have a time/date when the record is created =Now() and a person's
date of birth. You could then use this information at any time to display /
print the age at the time the record was created.

The time/date stamp that the record is created would be much more useful for
other operations than simply storeing the age of a person at some time in the
past.

Hope this helps

 

[Bonnie]

Hi Joseph

Thank You very much for you feedback, could you send me a link or
instructions on how to createa query for calculations please.

Regards

Bonnie

 

[Bonnie]

Hi

Thanks for your help, but the code does not work, I have replaced the names
with the names I have used, but still comesup with Name#.

Please could you break down the code or give me tips on how to resolve this.

Regards

Bonnie

Hi Bonnie

Of course you could save the results of a form's text box into a field
me.XXX=DateDiff("yyyy",[DateofBirthField],Date())-IIf(Format([DateofBirthField],"mmdd")>Format(Date(),"mmdd"),1,0)
but what would be the point.

You can have a time/date when the record is created =Now() and a person's
date of birth. You could then use this information at any time to display /
print the age at the time the record was created.

The time/date stamp that the record is created would be much more useful for
other operations than simply storeing the age of a person at some time in the
past.

Hope this helps


--
Wayne
Manchester, England.

I have a unbound text box that calculates the age of a person. I need this
age to be saved in a text box (this box only needs the age of the person at
date of admission).

I have used the following in my countrol source, but I don't know how to
tranfer this info into a field named age at admission. I also need to keep
this field as upto date as I may need to check current age sometimes to
determin a move.

Please can some one help as I am trying to create this database and due to
this small teething problem I am on a standstill.

 

[Wayne-I-M]

Hi Bonnie

The sample was only an example.

To make it work on a form
On your form you should have a time/date field (DateOfBirthField) with the
date of birth.

Create an unbound text box. Set this as the control source
=DateDiff("yyyy",[DateOfBirthField],Date())-IIf(Format([DateOfBirthField],"mmdd")>Format(Date(),"mmdd"),1,0)


Or to use this in a query with your table that contains details of the
people and the date of birth.

Open the query in design view. Create a new calculated column. Put this in
the field row

DateOfBirth:
DateDiff("yyyy",[TableName]![DateOfBirthField],Date())-IIf(Format([TableName]![DateOfBirthField],"mmdd")>Format(Date(),"mmdd"),1,0)

Change Table Name to what it really is
Change Date of birth field to what it really is


--
Wayne
Manchester, England.

Hi

Thanks for your help, but the code does not work, I have replaced the names
with the names I have used, but still comesup with Name#.

Please could you break down the code or give me tips on how to resolve this.

Regards

Bonnie

Hi Bonnie

Of course you could save the results of a form's text box into a field
me.XXX=DateDiff("yyyy",[DateofBirthField],Date())-IIf(Format([DateofBirthField],"mmdd")>Format(Date(),"mmdd"),1,0)
but what would be the point.

You can have a time/date when the record is created =Now() and a person's
date of birth. You could then use this information at any time to display /
print the age at the time the record was created.

The time/date stamp that the record is created would be much more useful for
other operations than simply storeing the age of a person at some time in the
past.

Hope this helps


--
Wayne
Manchester, England.

I have a unbound text box that calculates the age of a person. I need this
age to be saved in a text box (this box only needs the age of the person at
date of admission).

I have used the following in my countrol source, but I don't know how to
tranfer this info into a field named age at admission. I also need to keep
this field as upto date as I may need to check current age sometimes to
determin a move.

Please can some one help as I am trying to create this database and due to
this small teething problem I am on a standstill.

 

[Larry Daugherty]

You might find it instructive to read the "12 Rules of Databases" or
some similar title on
www.mvps.org/access
believe what you read there. That's an incredibly valuable site for
Access developers.

The applicable rule may be hard to winkle out but it amounts to never
storing calculated data within the database. To do so leads to
inevitable issues of concurrency. Sooner or later, the stored result
*will be wrong*.

As suggested elsewhere, the best solution to the age issue is to store
the birth date. Thereafter, calculate age whenever you need it.
Calculating is faster than disk retrieval and takes a lot less space.
If you have to know age as of a certain other point in time then make
that other point in time (registration date, sic.) a part of the
calculation. Use the calculated result in Reports or on Forms and
then let it go away. You can calculate it again when next needed.

You might resolve your calculation issue by writing it out as an
equation. Alternatively, change what's stored in your table to be the
date of birth and try to get that first textbox to work.

HTH
--
-Larry-
--

Hi

Thanks for your help, but the code does not work, I have replaced the names
with the names I have used, but still comesup with Name#.

Please could you break down the code or give me tips on how to resolve this.

Regards

Bonnie

Hi Bonnie

Of course you could save the results of a form's text box into a field

me.XXX=DateDiff("yyyy",[DateofBirthField],Date())-IIf(Format([DateofBi
rthField],"mmdd")>Format(Date(),"mmdd"),1,0)

but what would be the point.

You can have a time/date when the record is created =Now() and a person's
date of birth. You could then use this information at any time to display /
print the age at the time the record was created.

The time/date stamp that the record is created would be much more useful for
other operations than simply storeing the age of a person at some time in the
past.

Hope this helps