Default Value of field, from info in another field?

[E. J.]

I'm trying to create a field that will automatically
update a "Date", as the result of adding three years to
the date in the first field. Can an expression be written
directly in the "Default Value" property, Or, should I
write a query, then call that from "Default Value"? If the
latter, what would the expression be to make the call?

Thank you.

 

[Steve Schapel]

E.J.,

The Default Value is not applicable in this case. Default Value
refers to the point when a new record is started, and by the time you
have entered the value in your "First" field, it is too late for the
default value of the "Date" field to be applied. It would be possible
to assign the value of the "Date" field on the After Update event of
the "First" field's control on your form, using a macro or VBA
procedure, e.g.
Me.YourDateField = DateAdd("yyyy",3,Me.YourFirstField)
But before you go down that track, I would expect that what you are
trying to do here is really an invalid purpose. If the value of the
"Date" field will always be 3 years later than the value in the
"First" field, it should not be stored in the database anyway. This
flouts one of the principles of database design. Whenever you need
this date for your purposes on form or report, it should be derived at
the time via a calculated field in a query, or via an expression
within the control source of a textbox on the form or report itself.

- Steve Schapel, Microsoft Access MVP

 

[E. J.]

-----Original Message-----
E.J.,

The Default Value is not applicable in this case. Default Value
refers to the point when a new record is started, and by the time you
have entered the value in your "First" field, it is too late for the
default value of the "Date" field to be applied. It would be possible
to assign the value of the "Date" field on the After Update event of
the "First" field's control on your form, using a macro or VBA
procedure, e.g.
Me.YourDateField = DateAdd("yyyy",3,Me.YourFirstField)
But before you go down that track, I would expect that what you are
trying to do here is really an invalid purpose. If the value of the
"Date" field will always be 3 years later than the value in the
"First" field, it should not be stored in the database anyway. This
flouts one of the principles of database design. Whenever you need
this date for your purposes on form or report, it should be derived at
the time via a calculated field in a query, or via an expression
within the control source of a textbox on the form or report itself.

- Steve Schapel, Microsoft Access MVP




.
"Whenever you need this date for your purposes on

form or report, it should be derived at the time via a
calculated field in a query..."

Thanks, Steve. I'm going to change the timing of this
data retrieval, as you suggested.

Appreciate your help,
E. J.