Subtracting Dates

[Ifollowhim]

I have a (installation) date in a table that I put in manually. It is
the date we want to complete a job. In a form that uses data from that
table I want to add a text box that is bound to the installation date
but I want to subtract 7 days from the installation date. I tried
using [installation date] - 7 but that didn't work.

How can I have a text box display another's cells date - 7 days?

Thanks

[John Welch]

check out datediff in the help
-John

<If********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have a (installation) date in a table that I put in manually. It is
the date we want to complete a job. In a form that uses data from that
table I want to add a text box that is bound to the installation date
but I want to subtract 7 days from the installation date. I tried
using [installation date] - 7 but that didn't work.

How can I have a text box display another's cells date - 7 days?

Thanks

[Jeff Smith]

<If********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have a (installation) date in a table that I put in manually. It is
the date we want to complete a job. In a form that uses data from that
table I want to add a text box that is bound to the installation date
but I want to subtract 7 days from the installation date. I tried
using [installation date] - 7 but that didn't work.

How can I have a text box display another's cells date - 7 days?

Thanks

In addition to John's posting, also look at DateAdd in the help file. This
is better for adding or subtracting a number from a date than DateDiff.
DateDiff is better for comparing 2 dates.
Note: Look in the VBA help file for the 2 functions, not the Access help
file.

Jeff

[Br]

If********@gmail.com wrote:

I have a (installation) date in a table that I put in manually. It is
the date we want to complete a job. In a form that uses data from
that table I want to add a text box that is bound to the installation
date but I want to subtract 7 days from the installation date. I
tried using [installation date] - 7 but that didn't work.

How can I have a text box display another's cells date - 7 days?

Thanks

= DateDiff("d", -7, [InstallationDate])
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[John Welch]

Oops, thanks Jeff. Yes dateAdd is what you want, not datediff.

"Jeff Smith" <No****@Not.This.Address> wrote in message
news:dn**********@lust.ihug.co.nz...

<If********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have a (installation) date in a table that I put in manually. It is
the date we want to complete a job. In a form that uses data from that
table I want to add a text box that is bound to the installation date
but I want to subtract 7 days from the installation date. I tried
using [installation date] - 7 but that didn't work.

How can I have a text box display another's cells date - 7 days?

Thanks

In addition to John's posting, also look at DateAdd in the help file. This
is better for adding or subtracting a number from a date than DateDiff.
DateDiff is better for comparing 2 dates.
Note: Look in the VBA help file for the 2 functions, not the Access help
file.

Jeff

[Wayne Morgan]

[installation date] - 7 should have worked, depending on how you entered it.
Did you place this in the Control Source of the textbox? Did you put an
equal sign in front of it? It is possible that the value is being treated as
text instead of as a date. Is the textbox that you enter the date into bound
to a field that is defined with a data type of Date/Time? I just tried your
entry to double check and it worked.

Does the control that has the date have the same name as the field it is
bound to? This will sometimes cause a problem when you try to refer to it,
Access doesn't know which to refer to, the control or the field, although it
usually doesn't matter if it would just pick one.

--
Wayne Morgan
MS Access MVP
<If********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

I have a (installation) date in a table that I put in manually. It is
the date we want to complete a job. In a form that uses data from that
table I want to add a text box that is bound to the installation date
but I want to subtract 7 days from the installation date. I tried
using [installation date] - 7 but that didn't work.

How can I have a text box display another's cells date - 7 days?

Thanks

[Linds]

Thanks for the help, Wayne I got your way to work I had forgotton to
put the = sign

But another question related to this.

I added this in a text box inside of a form. But the answer to the
equation =[installation date] - 7 does not appear in the actual table.
The reason that I want it to appear in the table is so I can make a
report with the information. On the contrary it seems at least when
using the wizard that I am not able to make a report off of a form.

So in summary. I want to be able to use equations to control what is
displayed in a column. But if I use a form and go to
properties/control source I seem to only be able to view that
information when I am looking at that paticular form, which means I
can't make a report out of that info.

I don't know if the last paragraph was a summary or just as confusing
but I would appreciate anyones help. thanks

[Jana]

Linds:

You can have a calculated field on a report the same way you have it on
your form. If it's always a calculated value, there is no real reason
to store it in a table since you can calculate it any time you need it.
If you need to parameterize your report based on the result of the
calculation, then build the expression into a query and base your
report on the query. For instance, you could type in something like
this in a blank column for your query:

CompletionDate: DateDiff("d", -7, [InstallationDate])

Then, you can put criteria to limit the records returned and you can
reference the field as CompletionDate in your report.

HTH,
Jana

[Linds]

Jana, thanks that definatly helps me out!