Access:adding an "age" field which updates

[FoolinTheRain]

Hi,

I'm a novice access user stuck on a database that I'm creating. Here is what I would like to do:

1. I need to add a field titled "age" to a form that I have already created. I can't figure out how to add a field to a form that has already been created.

2. I need to create a formula which would subtract the current year from the manufacturing year (entered as a date **/**/** in a field already created). I would like the age field that is created to be updated automatically, depending on the current year.

Any thoughts?

Thanks

[BassFreq]

For the form to have fields, it must be tied to a table or a query in some way. You can determine which table or query its tied into by opening the form in design view, then press the F4 key to see the properties (or View>Properties). The "Record Source" under the Data tab will tell you the name.

If the form was tied to a table, to add the field, you'll need to create a new query. In this new query, include the table or query that the form is already based on. Bring all the fields into the query.

If the form was tied to a query, you'll just add the new field into the old query.

On the far right, add your new field. It'll look something like this:

AGE: Year([MNFCTR_DT] - now())
for the exact age, or
AGE: Year([MNFCTR_DT]) - Year(now())
for the age as you described it.
(btw- I'm using Access 2002, things may be a little different in your version if its different)

Now, save this query. If the query had a different name than the original record source in the form, go back to the form and change the record source to the name of your new query.

I hope this helps and isn't too confusing to follow.

[Emily]

Can't you just add a text box and put a formula in the control source?

[BassFreq]

That will almost do the trick, although this doesn't really add a field to the form (which was how you asked the question).

The problem with putting the formula in a text box is: the formula will put the field name in brackets( [] ). Bracketed names in a form will refer to other items in the form.

So, the way to go about doing this is:
Have a text box on the form that shows the manufacturing date, (let's say the field name is [MFG_DT]. If you don't want that visible on the form, you can set the visible property to No.

The Name of this text box will either be MFG_DT or something like TextBox42. If its not MFG_DT, change it to MFG_DT.

Now, add you're text box to show the age. My age formula was WAY off before, so use this one as the control source instead:

=DateDiff("yyyy",[MFG_DT],Now())

Now when you reference the MFG_DT, you're actually referencing the other text box which is referencing the table field. Microsoft set it up this way intentionally just to aggrevate people, so don't feel bad.

[FoolinTheRain]

Thanks Bassfreq, that worked great!

Can I link that box that has my age formula on the form back to my original table? I made a new field on my table after I added the age textbox to my form, but I can't seem to get it to update in the actual table after entering information on the form for the "age" field. thanks!

[FoolinTheRain]

I figured it out. I used the afterupdate event. Thanks!

[SamChevre]

Thanks Bassfreq, that worked great!

Can I link that box that has my age formula on the form back to my original table? I made a new field on my table after I added the age textbox to my form, but I can't seem to get it to update in the actual table after entering information on the form for the "age" field. thanks!

I know you figured it out, but another way to do this (if the data source is a table) is to use an Update query. Back the table up first (copy, paste, and rename); the add a field called "age" to the table in design view. Now create a new query, linked to the table; put age in the top row, and change the query to an update query. Now put the formula for age in the "update to" row, switch to datasheet view to be sure it is working, and run the query.

You will have a new field, entitled "age ," in the table; if it looks right, delete your backup table and you're done.

I know you don't need it for this project, but updte queries are very useful so I thought I'd mention it.

[Brad Gile]

Microsoft set it up this way intentionally just to aggrevate people, so don't feel bad.

ROFL! I love it.
I'm saving this quote. Fits Access to a T! :D


Brad<---not an Access fan