dateadd

[Guest]

I am trying to set up my database. I have one field that is named
"firstsurvey" and I need the field "secondsurvey" to display the date six
months from the date in "firstsurvey". When I try to set a default formula
in design view (secondsurvey=DateAdd("m", 6, firstsurvey) or simply
(DateAdd("m", 6, firstsurvey"), I get an error message saying it does not
recognize firstsurvey. Help, I have tried everything!

 

[John Vinson]

I am trying to set up my database. I have one field that is named
"firstsurvey" and I need the field "secondsurvey" to display the date six
months from the date in "firstsurvey". When I try to set a default formula
in design view (secondsurvey=DateAdd("m", 6, firstsurvey) or simply
(DateAdd("m", 6, firstsurvey"), I get an error message saying it does not
recognize firstsurvey. Help, I have tried everything!

A Default property in a table cannot reference any other field in the
table, because the Default value is applied the instant a new record
is created. firstsurvey has not yet come into existance at this point,
so there's no way to use it!

If the secondsurvey is ALWAYS six months later, don't store it in the
table at all; calculate it on the fly in a Query.

If it should just default to six months later, but allow for changes,
you'll need to use a little bit of VBA code in a Form to "push" the
value into it. (Table datasheets don't have usable events). Code would
be something like:

Private Sub txtFirstSurvey_AfterUpdate()
If IsNull(Me!txtSecondSurvey) Then
Me!txtSecondSurvey = DateAdd("m", 6, Me!txtFirstSurvey)
End If
End Sub

using your own control names of course.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps