Calculated date field in form to table

[sendahook]

Now I understand why storing a calculated field in a table is usually a
bad idea and you should just recalculate it later if needed. I've read
about 10,000 posts asking this same question!! However, I have a form
which lets the user choose a number of days from a pull-down combo box
and performs an ending date calculation from their choice ie...
There is a [STARTINGDATE] field (which defaults to todays date) and an
[ENDING DATE] field which used to be just manually entered by the user.
I wanted to make a combo box that lets the user select exactly how many
days in the future they want for the ending date (1 day, 7 days, etc.)
and have the [ENDINGDATE] field calculated from their choice.

Since the [ENDINGDATE] field will never change, I don't see this as
being a problem to store directly into the table since my query that is
used to print the reports is based from that table. I've read some
posts on how to force the calculated field value into the table by
using the BeforeUpdate / AfterUpdate properties but I must not be doing
it right since the calculated field just shows "#Name?" Can someone
walk me through the process. Or maybe I'm just going about the whole
thing the wrong way. Any help would be appreciated.

Thanks

 

[Jesse Aviles]

You can write code in the combo box AfterUpdate event similar to:

EndingDate = StartingDate + CInt(ComboBoxValue)

--
Jesse Avilés
[email protected]

What is a whale doing ot the bottom of the ocean?
Waiting for a-while.

Reply Only To The Newsgroup

 

[John Vinson]

Now I understand why storing a calculated field in a table is usually a
bad idea and you should just recalculate it later if needed.

There is a [STARTINGDATE] field (which defaults to todays date) and an
[ENDING DATE] field which used to be just manually entered by the user.
I wanted to make a combo box that lets the user select exactly how many
days in the future they want for the ending date (1 day, 7 days, etc.)
and have the [ENDINGDATE] field calculated from their choice.

Something like this should work in the Combo Box's AfterUpdate event:

Private Sub cboDuration_AfterUpdate()
Me![Endingdate] = DateAdd("d", Me!cboDuration, CDate(Me![STARTINGDATE]))
End Sub

John W. Vinson/MVP