DateAdd

[Citybird]

I have a field "Start Date" (dd/mm/yyyy) and a "Term" field (__yy__mm). I
want to create a calc "End Date" field that shows the date result
(dd/mm/yyyy) when I add the number of years to the start date as follows:

Start Date 05/11/2007 add 25 yy 00mm = [End Date].

All help appreciated!

 

[Brendan Reynolds]

I have a field "Start Date" (dd/mm/yyyy) and a "Term" field (__yy__mm). I
want to create a calc "End Date" field that shows the date result
(dd/mm/yyyy) when I add the number of years to the start date as follows:

Start Date 05/11/2007 add 25 yy 00mm = [End Date].

All help appreciated!

You can do this in a query, or in the control source property of a text box
on a form or report.

Here's an example of a query that adds 25 years to a date field. This
example uses the "Orders" table from Microsoft's "Northwind" sample
database.

SELECT DateAdd("y",25,[Order Date]) AS EstDel
FROM Orders;

Alternatively, as the control source of a text box on a form or report, the
expression would look like so ...

=DateAdd("y",25,[Order Date])

 

[John Spencer]

I would store the term as a number of months in a number field. Or store
the term in two number fields - TermYears and TermMonths. That would make
the calculation of the end date easy.

IF term field is as you have indicated then
Val(Term) will return the number of years
Val(Mid(Term,INstr(Term,"yy")+2)) should return the number of months

So the following should work to calculate the end date
DateSerial(Year(StartDate) + Val(Term),Month(StartDate)+
Val(Mid(Term,InStr(Term,"yy")+2)), Day(StartDate))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Citybird]

Only problem with this is that the term will vary all the time and is not
always 25 years.

I have a field "Start Date" (dd/mm/yyyy) and a "Term" field (__yy__mm). I
want to create a calc "End Date" field that shows the date result
(dd/mm/yyyy) when I add the number of years to the start date as follows:

Start Date 05/11/2007 add 25 yy 00mm = [End Date].

All help appreciated!

You can do this in a query, or in the control source property of a text box
on a form or report.

Here's an example of a query that adds 25 years to a date field. This
example uses the "Orders" table from Microsoft's "Northwind" sample
database.

SELECT DateAdd("y",25,[Order Date]) AS EstDel
FROM Orders;

Alternatively, as the control source of a text box on a form or report, the
expression would look like so ...

=DateAdd("y",25,[Order Date])

 

[Brendan Reynolds]

As John indicates elsewhere in this thread, simply replace the literal value
(25 in the example) with a reference to the column or control that contains
the value.

--
Brendan Reynolds

Only problem with this is that the term will vary all the time and is not
always 25 years.

I have a field "Start Date" (dd/mm/yyyy) and a "Term" field (__yy__mm).
I
want to create a calc "End Date" field that shows the date result
(dd/mm/yyyy) when I add the number of years to the start date as
follows:

Start Date 05/11/2007 add 25 yy 00mm = [End Date].

All help appreciated!

You can do this in a query, or in the control source property of a text
box
on a form or report.

Here's an example of a query that adds 25 years to a date field. This
example uses the "Orders" table from Microsoft's "Northwind" sample
database.

SELECT DateAdd("y",25,[Order Date]) AS EstDel
FROM Orders;

Alternatively, as the control source of a text box on a form or report,
the
expression would look like so ...

=DateAdd("y",25,[Order Date])

 

[Citybird]

So easy when you know how...many thanks!

As John indicates elsewhere in this thread, simply replace the literal value
(25 in the example) with a reference to the column or control that contains
the value.

--
Brendan Reynolds

Only problem with this is that the term will vary all the time and is not
always 25 years.

I have a field "Start Date" (dd/mm/yyyy) and a "Term" field (__yy__mm).
I
want to create a calc "End Date" field that shows the date result
(dd/mm/yyyy) when I add the number of years to the start date as
follows:

Start Date 05/11/2007 add 25 yy 00mm = [End Date].

All help appreciated!


You can do this in a query, or in the control source property of a text
box
on a form or report.

Here's an example of a query that adds 25 years to a date field. This
example uses the "Orders" table from Microsoft's "Northwind" sample
database.

SELECT DateAdd("y",25,[Order Date]) AS EstDel
FROM Orders;

Alternatively, as the control source of a text box on a form or report,
the
expression would look like so ...

=DateAdd("y",25,[Order Date])

 

[Fariza Nacereddine]

hi

As John indicates elsewhere in this thread, simply replace the literal
value (25 in the example) with a reference to the column or control that
contains the value.

--
Brendan Reynolds

Only problem with this is that the term will vary all the time and is not
always 25 years.

I have a field "Start Date" (dd/mm/yyyy) and a "Term" field (__yy__mm).
I
want to create a calc "End Date" field that shows the date result
(dd/mm/yyyy) when I add the number of years to the start date as
follows:

Start Date 05/11/2007 add 25 yy 00mm = [End Date].

All help appreciated!


You can do this in a query, or in the control source property of a text
box
on a form or report.

Here's an example of a query that adds 25 years to a date field. This
example uses the "Orders" table from Microsoft's "Northwind" sample
database.

SELECT DateAdd("y",25,[Order Date]) AS EstDel
FROM Orders;

Alternatively, as the control source of a text box on a form or report,
the
expression would look like so ...

=DateAdd("y",25,[Order Date])