calculated field/control in a form

[megbowlstrike]

Hi. I have a form with a text field control called Retire Date. This
field needs to display a date that is calcuated by taking
tbl_Employee.birthDate (in mm/dd/yyyy) + 62 (years) = Retire Date
(needs to be mm/dd/yyyy format). How do I go about doing this? The
value also will need to be stored in a table.

I'm having trouble figuring out the formula. Then I also don't know
how to put the calculated field on the form. I played around with a
formula to compute the Retire Date year and couldn't get it to link up
with the control on the form. I created a query and the output in the
Retire Date control on the form was #Name?

Thanks,
Megan

 

[Douglas J. Steele]

Set the ControlSource of the text box to

=DateAdd("yyyy", 62, birthDate)

(including the equal sign)

 

[fredg]

Hi. I have a form with a text field control called Retire Date. This
field needs to display a date that is calcuated by taking
tbl_Employee.birthDate (in mm/dd/yyyy) + 62 (years) = Retire Date
(needs to be mm/dd/yyyy format). How do I go about doing this? The
value also will need to be stored in a table.

I'm having trouble figuring out the formula. Then I also don't know
how to put the calculated field on the form. I played around with a
formula to compute the Retire Date year and couldn't get it to link up
with the control on the form. I created a query and the output in the
Retire Date control on the form was #Name?

Thanks,
Megan

Add an unbound text control to your form.
Set it's control source to:
=DateAdd("yyyy",62,[BirthDate])

This value should NOT be stored in your table. Anytime you need the
employee's retirement date, simply recalculate it, as above.

 

[megbowlstrike]

Hi. I have a form with a text field control called Retire Date. This
field needs to display a date that is calcuated by taking
tbl_Employee.birthDate (in mm/dd/yyyy) + 62 (years) = Retire Date
(needs to be mm/dd/yyyy format). How do I go about doing this? The
value also will need to be stored in a table.

I'm having trouble figuring out the formula. Then I also don't know
how to put the calculated field on the form. I played around with a
formula to compute the Retire Date year and couldn't get it to link up
with the control on the form. I created a query and the output in the
Retire Date control on the form was #Name?

Thanks,
Megan

Add an unbound text control to your form.
Set it's control source to:
=DateAdd("yyyy",62,[BirthDate])

This value should NOT be stored in your table. Anytime you need the
employee's retirement date, simply recalculate it, as above.

Thanks for the tips! I created an unbound text control and used this
formula:

=DateAdd("yyyy",62,[tbl_Umbrella_EE Data_Constant]!Birthdate)

Instead of properly calculating the date, it's giving me this output:
#Name?

What am I doing wrong?

 

[megbowlstrike]

Add an unbound text control to your form.
Set it's control source to:
=DateAdd("yyyy",62,[BirthDate])

This value should NOT be stored in your table. Anytime you need the
employee's retirement date, simply recalculate it, as above.

Thanks for the tips! I created an unbound text control and used this
formula:

=DateAdd("yyyy",62,[tbl_Umbrella_EE Data_Constant]!Birthdate)

Instead of properly calculating the date, it's giving me this output:
#Name?

What am I doing wrong?- Hide quoted text -

- Show quoted text -

It should be noted that the birthdate field is on a form called
frm_DataEntry and the form that the calculated field is on is called
frm_Job. So the two fields are on different forms. I got the
calculation to work by putting the unbound text control on the
DataEntry form that also contains the birthdate field. However it is
not working when I try to do the calculation on a different from than
the one that contains birthdate. Can this be done or does the
calculated text box need to be on the same form as the birthdate
field?

 

[Douglas J. Steele]

Thanks for the tips! I created an unbound text control and used this
formula:

=DateAdd("yyyy",62,[tbl_Umbrella_EE Data_Constant]!Birthdate)

Instead of properly calculating the date, it's giving me this output:
#Name?

What am I doing wrong?- Hide quoted text -

- Show quoted text -

It should be noted that the birthdate field is on a form called
frm_DataEntry and the form that the calculated field is on is called
frm_Job. So the two fields are on different forms. I got the
calculation to work by putting the unbound text control on the
DataEntry form that also contains the birthdate field. However it is
not working when I try to do the calculation on a different from than
the one that contains birthdate. Can this be done or does the
calculated text box need to be on the same form as the birthdate
field?

The field in query needs to be in the underlying recordset of the form
(although it doesn't need to actually be displayed on the form). On the
other hand, you can refer to controls on other forms, so you could try
something like:

=DateAdd("yyyy",62,[Forms]![frm_DataEntry]!Birthdate)

 

[fredg]

Hi. I have a form with a text field control called Retire Date. This
field needs to display a date that is calcuated by taking
tbl_Employee.birthDate (in mm/dd/yyyy) + 62 (years) = Retire Date
(needs to be mm/dd/yyyy format). How do I go about doing this? The
value also will need to be stored in a table.

I'm having trouble figuring out the formula. Then I also don't know
how to put the calculated field on the form. I played around with a
formula to compute the Retire Date year and couldn't get it to link up
with the control on the form. I created a query and the output in the
Retire Date control on the form was #Name?

Thanks,
Megan

Add an unbound text control to your form.
Set it's control source to:
=DateAdd("yyyy",62,[BirthDate])

This value should NOT be stored in your table. Anytime you need the
employee's retirement date, simply recalculate it, as above.

Thanks for the tips! I created an unbound text control and used this
formula:

=DateAdd("yyyy",62,[tbl_Umbrella_EE Data_Constant]!Birthdate)

Instead of properly calculating the date, it's giving me this output:
#Name?

What am I doing wrong?- Hide quoted text -

- Show quoted text -

It should be noted that the birthdate field is on a form called
frm_DataEntry and the form that the calculated field is on is called
frm_Job. So the two fields are on different forms. I got the
calculation to work by putting the unbound text control on the
DataEntry form that also contains the birthdate field. However it is
not working when I try to do the calculation on a different from than
the one that contains birthdate. Can this be done or does the
calculated text box need to be on the same form as the birthdate
field?

Forms don't contain data, they just display data. Tables actually
contain the data.

So, if the [Birthdate] field is NOT included in the form's record
source, you need to look it up in whatever table it is stored in. You
also need to use a Where Clause to return the correct person's birth
date.

=DateAdd("yyyy",62,DLookUp("[BirthDate]","[tbl_Umbrella_EE
Data_Constant]","[SomeIDFieldInTable] = " & [SomeIDFieldOnThisForm]))

You'll have to enter the correct ID field names.
The above assumes the ID field is a Number datatype.
If it is a Text datatype, then use:

..... DLookUp("[BirthDate]","[tbl_Umbrella_EE Data_Constant]",
"[SomeIDFieldInTable] = '" & [SomeIDFieldOnThisform] & "'"))

Look up, in VBA help files:
DLookUp
as well as
Restrict Data to a Subset of Records