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.-

ok... I'll spare you the lecture then! g
-
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/MVPThanks John...I tried the AfterUpdate event, but it's still not working.

I'm sure it's something simple I'm missing but can't figure it out as
of yet. I was wondering if I just redesigned my query a little to make
this work rather than forcing the calculated date from the form to the
table. I'll show you what I had and maybe you could tell me what I'm
doing wrong...

In the FORM: I have a [STARTINGDATE field (which defaults to today's date, but can be entered manually if
needed). Then I had a 2 column combo box pull-down menu [TIMEACTIVE]
field which lets the user select how many days they want the report to
be active for (1 Day, 3 Days, 10 Days, etc.) with the number being the
actual stored value( 1, 3, 10, etc.)

In the QUERY: I needed to just pull the records that are active, so I
needed to return all records that are between the [STARTINGDATE] and
the date that was entered to be x many days in the future ie... if the
starting date is 03/30/05 and 3 days was selected under the
[TIMEACTIVE] field, I need to query to find all records between
03/30/05 and 04/02/05. So, under the [STARTINGDATE] criteria I have:
<Date()
Under the [TIMEACTIVE] criteria I tried something like this:

DateAdd("d",[TIMEACTIVE],[STARTINGDATE]) I thought this would do the

calculation and give me a my desired results but no luck. Any
suggestions on how to enter the criteria correctly under the
[TIMEACTIVE] field?

Thanks for your patience in trying to understand my mess.

 

[John Vinson]

I needed to just pull the records that are active, so I
needed to return all records that are between the [STARTINGDATE] and
the date that was entered to be x many days in the future ie... if the
starting date is 03/30/05 and 3 days was selected under the
[TIMEACTIVE] field, I need to query to find all records between
03/30/05 and 04/02/05. So, under the [STARTINGDATE] criteria I have:
<Date()
Under the [TIMEACTIVE] criteria I tried something like this:

DateAdd("d",[TIMEACTIVE],[STARTINGDATE]) I thought this would do the

calculation and give me a my desired results but no luck. Any
suggestions on how to enter the criteria correctly under the
[TIMEACTIVE] field?

Since DateAdd() returns a Date/Time value, and TimeActive is a number
field not a date, it's not surprising that you get the wrong value!

Try a criterion on [Startingdate] of

= Date() AND <= DateAdd("d", [TIMEACTIVE], Date())

if I'm understanding you correctly.

John W. Vinson[MVP]