DateAdd in forms

[Guest]

Hi, at the moment im using
=DateAdd("d",[Days],[Starthire]) to calculate a date using an ezxisting date
and a number of days, however in the field that this is entered in the
control source of, it doesnt seem to put that date into the table, can anyone
help rectify this? thanks, WK

 

[Al Campagna]

Bilbo,
Your field is an "unbound" calculated field, therefore it is not associated with any
field in the table, and only "displays" the result of the calculation.
And that's the best way to do it...
There is no need to (and you shouldn't) save the calculated date (ex. EmpReviewDate).
Since you have the StartDate, and the number of days stored, you only need to display the
calculation result.
EmpReviewDate can always be recalculated from the StartDate and Days... "on the fly"
whenever you need it... in any subsequent form, query, or report.

 

[Guest]

Unless you want to be able to manually change the date from the date computed
by the expression you should not have a field in the table for the computed
value. Just use an unbound control in a form or report as you are doing, or
a computed column in a query, to get the date on the fly. Otherwise you are
introducing redundancy into the table, which leaves it exposed to the risk of
update anomalies.

If you do want to be able to edit the computed value, i.e. its only a
default which you might want to adjust, then make the ControlSource of the
text box the name of the field in the table and assign a value to it in the
AfterUpdate event procedures of both the Days and Starthire controls with
something like:

Const NULLERROR = 94

On Error Resume Next
Me.FinishDate = DateAdd("d",Me.Days,Me.Starthire)
Select Case Err.Number
Case 0
' no error so do nothing
Case NULLERROR
' anticipate error so do nothing
Case Else
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select

The error handling is necessary as you can't predict in which order the user
will fill in the Starthire and Days controls.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

Consider not doing this. If you know that the "calculated" value is this
([Days]) number of days, and you know that the person was hired on this
([StartHire]) date, you can (and probably should) do the calculation in a
query, "on the fly".

In rare instances, you'd need to save a calculated value in a table. But if
you do this, you'll also need to build the synchronization routines to
ensure that the calculated value gets updated correctly if someone alters
the [StartHire] value ... say, due to a typo.

Regards

Jeff Boyce
Microsoft Office/Access MVP