Calculated date field

[vincent]

I have 2 date fields.

the second should be calculated base on the first, here
is what I have tried

Default value =DateAdd(-3,"w","First Date")

should be 3 weekdays prior to the First date

Access don't let that be saved!!!

What is wrong?

 

[Ken Snell]

Are you trying to do this directly in the table? In a table, ACCESS doesn't
allow you to use the value of another field in the default value expression.

Please, more info about where you're trying to do this.

 

[vincent]

Yes I was trying in the table! Has I would do in major
database.

Where do I need to do it, in the a form? I would like it
to update value if the first date is change. Has I would
in Excel for exemple.

 

[Ken Snell]

Yes, you would use a form to do this. In ACCESS, one should never
edit/enter/delete data directly in tables; use forms for that purpose.

In the form, you could use the form's before insert event to update this
field.

 

[Guest]

If I understand correctly I should use the before update
event, so that change in first date is always reflected.

but what should I put in the event itself
tableName!Second Date=DateAdd(-3,[w],Tablename!first Date)

I don't know much about access yet, I use to work with
other RDBMS!

 

[Ken Snell]

Use the field names (they should be part of the form's recordset):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Second Date].Value = DateAdd(-3, "w", Me.[First Date])
End Sub


--
Ken Snell
<MS ACCESS MVP>

If I understand correctly I should use the before update
event, so that change in first date is always reflected.

but what should I put in the event itself
tableName!Second Date=DateAdd(-3,[w],Tablename!first Date)

I don't know much about access yet, I use to work with
other RDBMS!

-----Original Message-----
Yes, you would use a form to do this. In ACCESS, one should never
edit/enter/delete data directly in tables; use forms for that purpose.

In the form, you could use the form's before insert event to update this
field.

--
Ken Snell
<MS ACCESS MVP>




.

 

[Jeff Boyce]

Vincent

Uhmmm, you have a field that gets a data entered, and you need to know a
date three weekdays prior? Storing a calculated value is generally not a
very good idea. Besides being redundant, you'll have to build the code to
ensure that the fields are synchronized (if one changes, what should happen
to the other).

And you'd have to go through all your code and forms and reports if the
difference changed to "4 weekdays".

Instead, just record the one date, and use a query to calculate, on the fly,
the second.

Good luck

Jeff Boyce
<Access MVP>