update date field from another field

[Mi]

Help
I have a RecDate field on form. I would like another field, DueDate to be 15 days after the RecDate field.
Considerations: Need to be able to manually override the DueDate field if needed. Would like to the have the DueDate to always be a Monday, but that is more of a wish.

I looked at DateAdd but nothing I have tried seems to work. Not sure where to put it.

Mi

 

[Andrew Fergus]

Hi Mi
If you go into the form design, right click the [RecDate] field -> Properties -> Event Tab -> After Update -> Click the ... button to activate the Builder -> Code Builder -> Ok -> enter this (without the two outermost quotes) "[DueDate] = DateAdd("d",15,[RecDate])" -> Save and close the VB window -> Save the form.

The code should look like this (before you close the VB window):

Private Sub RecDate_AfterUpdate()
[DueDate] = DateAdd("d", 15, [RecDate])
End Sub

This will update the 'DueDate' field to 15 days after the 'RecDate'.

If you want to force it to be a Monday (the following Monday, if not already a Monday), then use this code instead :

[DueDate] = DateAdd("d", IIf(Weekday(DateAdd("d", 15, [RecDate])) < 3, 2 - Weekday(DateAdd("d", 15, [RecDate])), 9 - Weekday(DateAdd("d", 15, [RecDate]))), DateAdd("d", 15, [RecDate]))

HTH, Andrew.

 

[Mi]

Thanks! Works like a dream. I was trying to put the code in the DueDate field rather that than the RecDate. The Monday thing was a big BONUS.

Your an Angel!

Mi