date expressions

[Guest]

How do I get a field to calculate a future date based on the date in another
field? For example, I want to be able to enter a date in a field and add 10
days to ifferent field automaticaly.

 

[Guest]

To add days to existing date, you can use that
=DateField + 10

Or, check help on DateAdd function
=DateAdd("d",10,DateField) ' To add 10 days
=DateAdd("yyyy",10,DateField) ' To add 10 years
And more

 

[Brendan Reynolds]

Use the DateAdd function. For example, in a query ...

SELECT tblTest.TestDate, DateAdd("d",[TestDate],10) AS TestDatePlus10
FROM tblTest;

In query design view, the expression looks like this (in the 'Field' row in
the design grid) ...

TestDatePlus10: DateAdd("d",[TestDate],10)

In the Control Source property of a text box on a form or report, it would
look like this ...

= DateAdd("d",[TestDate],10)

See "DateAdd Function" in the help files for more information.

 

[Guest]

Thanks a lot to both of you. It works perfectly! My database is finally
complete. No more staying up late at night with visions of expressions in my
head.

 

[Guest]

Ive been searching for similar date queries and this seems to be nearest to
what im trying to do but it's not quite the same. I don’t know if this is
possible but I am trying to make a query (or Function) to use in a form via a
text box ‘Season’ that shows a Season date.e.g ‘Season’ = 05-06. The start
date is always 1st September regardless of the Year. I want to create
‘Season’ starting on the 1st of September of the current year according to
the system date and runs for 12 Months because I have a membership database
that takes registrations from that date each year similar to Autumn start
time for schools.. I would like to use the Year to allocate the season, e.g.
1st September 2005 –31st August 2006 = Season 05-06. I want it to
automatically update each following Year, i.e. 06-07, 07-08. If necessary I
could use a StartYear 2005 and EndYear2006 and then run a query to
concatenate the last 2 digits of each Year resulting in 05-06. Is this
possible? I don’t want to have to use a parameter query that asks the user
for an input date but want it based on the system date so it will
automatically update itself.

Use the DateAdd function. For example, in a query ...

SELECT tblTest.TestDate, DateAdd("d",[TestDate],10) AS TestDatePlus10
FROM tblTest;

In query design view, the expression looks like this (in the 'Field' row in
the design grid) ...

TestDatePlus10: DateAdd("d",[TestDate],10)

In the Control Source property of a text box on a form or report, it would
look like this ...

= DateAdd("d",[TestDate],10)

See "DateAdd Function" in the help files for more information.

--
Brendan Reynolds

How do I get a field to calculate a future date based on the date in
another
field? For example, I want to be able to enter a date in a field and add
10
days to ifferent field automaticaly.

 

[Guest]

I have read all of these threads and they don't seem to address my needs. I
have table named "tblReview" with a field named "BeginDate", and I need to
have a second field named "DueDate" which auto calculates. The calculation
need to be the BeginDate + 60 days. I can enter the formula "=
[BeginDate]+60 into the form field and get the correct calculation, but I
need the calculated response to be stored as a field in the table so that can
be used to run queries and reports. Any ideas?


Thanks!

Use the DateAdd function. For example, in a query ...

SELECT tblTest.TestDate, DateAdd("d",[TestDate],10) AS TestDatePlus10
FROM tblTest;

In query design view, the expression looks like this (in the 'Field' row in
the design grid) ...

TestDatePlus10: DateAdd("d",[TestDate],10)

In the Control Source property of a text box on a form or report, it would
look like this ...

= DateAdd("d",[TestDate],10)

See "DateAdd Function" in the help files for more information.

--
Brendan Reynolds

How do I get a field to calculate a future date based on the date in
another
field? For example, I want to be able to enter a date in a field and add
10
days to ifferent field automaticaly.

 

[Douglas J. Steele]

You don't need the calculated date stored in the table.

Create a query that has a computed field that returns the calculated
DueDate. Use the query rather than the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have read all of these threads and they don't seem to address my needs.
I
have table named "tblReview" with a field named "BeginDate", and I need to
have a second field named "DueDate" which auto calculates. The
calculation
need to be the BeginDate + 60 days. I can enter the formula "=
[BeginDate]+60 into the form field and get the correct calculation, but I
need the calculated response to be stored as a field in the table so that
can
be used to run queries and reports. Any ideas?


Thanks!

Use the DateAdd function. For example, in a query ...

SELECT tblTest.TestDate, DateAdd("d",[TestDate],10) AS TestDatePlus10
FROM tblTest;

In query design view, the expression looks like this (in the 'Field' row
in
the design grid) ...

TestDatePlus10: DateAdd("d",[TestDate],10)

In the Control Source property of a text box on a form or report, it
would
look like this ...

= DateAdd("d",[TestDate],10)

See "DateAdd Function" in the help files for more information.

--
Brendan Reynolds

How do I get a field to calculate a future date based on the date in
another
field? For example, I want to be able to enter a date in a field and
add
10
days to ifferent field automaticaly.