Subtract 3 weeks from a text box and show value in another?

T

TKM

OK sho I put this ???? in what text box? the one that I want to subtract from
or? Also do I change any of the names you have such as (original date) or?

Thank you for your help
 
K

Klatuu

Make it the control source property of the text box where you want 3 week old
date to show:
= dateadd("ww",-3,OriginalDate)

Change OriginalDate to the name of the text box with the date you want to
subtract 3 weeks from.

If you come back and say you can't do that because it is a bound control, I
will not be sympathetic. Calculated values should not be stored in tables.
No "yeah, but"'s. There is no reason to store this value.
 
J

Jeff Boyce

You could take the suggestion literally and try it ... and it will fail. I
suspect, since we can't see your actual data/fieldnames/etc., that the
suggestion is generic, and you'll have to use your data in place of the
general outline.

As for where you can ... put it, where did you want it to show up? If you
are working in a form, and if the new value is for display purposes only
(i.e., you are not trying to store it), you could enter everything from the
"=" and to the right in the Control Source property of that control on the
form.

If you are trying to come up with a way to store the calculated value, STOP!
It is rarely necessary to store a calculated value, and if you decide you
must, you will also have to build the routines to keep that calculated value
in synch when the underlying component(s) changes.

Instead of storing a calculated value, consider using a query to do the
calculation when you need it. Create a new field in a query, something
like:

NewDateField: DateAdd("ww",-3,OriginalDate)

NOTE: DO NOT use the name of the original date field literally - use your
own.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

TKM

Thank you very much!

Jeff Boyce said:
You could take the suggestion literally and try it ... and it will fail. I
suspect, since we can't see your actual data/fieldnames/etc., that the
suggestion is generic, and you'll have to use your data in place of the
general outline.

As for where you can ... put it, where did you want it to show up? If you
are working in a form, and if the new value is for display purposes only
(i.e., you are not trying to store it), you could enter everything from the
"=" and to the right in the Control Source property of that control on the
form.

If you are trying to come up with a way to store the calculated value, STOP!
It is rarely necessary to store a calculated value, and if you decide you
must, you will also have to build the routines to keep that calculated value
in synch when the underlying component(s) changes.

Instead of storing a calculated value, consider using a query to do the
calculation when you need it. Create a new field in a query, something
like:

NewDateField: DateAdd("ww",-3,OriginalDate)

NOTE: DO NOT use the name of the original date field literally - use your
own.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
J

John W. Vinson

How do I subtract 3 weeks from one date field and have it show in another?
Click to expand...

The second field should NOT EXIST in your table, if it's to always be three
weeks prior to the first date.

Instead, put a textbox on your Form with a control source

=DateAdd("d", -21, [FirstTextBoxName])

to automatically calculate the date and display it (but not store it).

John W. Vinson [MVP]
 
T

TKM

Jeff,

Thanks for your help. I know its notthe best to store calulated fields.
However I do need that calulation stored in my table. I set it up in the
query to work but I do need it to populated +3 weeks when text box 1 has a
date in it. Do you know of a way for this to work.

Thanks again for all your help!
 
J

Jeff Boyce

It sounds like you've decided your solution is to store a value.

If you'll describe the "problem" you are solving by doing this, the
newsgroup's readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top