Adding input to date

[stephen.belyea]

Hopefully the last time I will have to bug everyone!

Within the BMP database I am working on, I am trying to find a way to
calculate the next inspection date for each BMP, given the previous
inspection date [tblBMP.InspDate] and the inspection period
[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
every 3 years). I've seen examples of simply adding a static value to
the date, but how would I go about about using an input number and
adding it to the year of the previous inspection date?

Thanks again everyone!

 

[Guest]

If all are in years then use the DateAdd function.
DateAdd("yyyy", [tblBMP.InspPeriod], [tblBMP.InspDate])

 

[stephen.belyea]

If all are in years then use the DateAdd function.
DateAdd("yyyy", [tblBMP.InspPeriod], [tblBMP.InspDate])

--
KARL DEWEY
Build a little - Test a little

Hopefully the last time I will have to bug everyone!

Within the BMP database I am working on, I am trying to find a way to
calculate the next inspection date for each BMP, given the previous
inspection date [tblBMP.InspDate] and the inspection period
[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
every 3 years). I've seen examples of simply adding a static value to
the date, but how would I go about about using an input number and
adding it to the year of the previous inspection date?

Thanks again everyone!

Thanks for the response Karl.

[tblInspection.InspDate] is in date format, but
[tblInspection.InspPeriod] will simply be a single digit number. As an
example I have a date of 3/13/1999 for the last inspection, and the
inspection period ends up being 3 years. I want to use data from a
Number field to change the value of the year in the Date field. Is
there any way to do this?

 

[Guest]

What I posted does exactly that. Did you try it?


--
KARL DEWEY
Build a little - Test a little

If all are in years then use the DateAdd function.
DateAdd("yyyy", [tblBMP.InspPeriod], [tblBMP.InspDate])

--
KARL DEWEY
Build a little - Test a little

Hopefully the last time I will have to bug everyone!

Within the BMP database I am working on, I am trying to find a way to
calculate the next inspection date for each BMP, given the previous
inspection date [tblBMP.InspDate] and the inspection period
[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
every 3 years). I've seen examples of simply adding a static value to
the date, but how would I go about about using an input number and
adding it to the year of the previous inspection date?

Thanks again everyone!

Thanks for the response Karl.

[tblInspection.InspDate] is in date format, but
[tblInspection.InspPeriod] will simply be a single digit number. As an
example I have a date of 3/13/1999 for the last inspection, and the
inspection period ends up being 3 years. I want to use data from a
Number field to change the value of the year in the Date field. Is
there any way to do this?

 

[John W. Vinson]

Hopefully the last time I will have to bug everyone!

Within the BMP database I am working on, I am trying to find a way to
calculate the next inspection date for each BMP, given the previous
inspection date [tblBMP.InspDate] and the inspection period
[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
every 3 years). I've seen examples of simply adding a static value to
the date, but how would I go about about using an input number and
adding it to the year of the previous inspection date?

Thanks again everyone!

DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

will add InspPeriod years to the date in InspDate...

John W. Vinson [MVP]

 

[stephen.belyea]

Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.

Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?

Thanks again!

Hopefully the last time I will have to bug everyone!

Within the BMP database I am working on, I am trying to find a way to
calculate the next inspection date for each BMP, given the previous
inspection date [tblBMP.InspDate] and the inspection period
[tblBMP.InspPeriod] which is in years (e.g. a given BMP is inspected
every 3 years). I've seen examples of simply adding a static value to
the date, but how would I go about about using an input number and
adding it to the year of the previous inspection date?

Thanks again everyone!

DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

will add InspPeriod years to the date in InspDate...

John W. Vinson [MVP]

 

[John W. Vinson]

Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.

Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?

I would strongly recommend that you NOT store the next inspection date in
tblBMP or in any other table, unless you want the calculated date to be just a
default which can be edited. To display it on a form just use

=DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

as the Control Source of a textbox on the form.

If you have a good reason to actually store it in the table, post back - it's
possible with a bit of VBA code, but it's just likely to be a bad idea to do
so.

John W. Vinson [MVP]

 

[stephen.belyea]

Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.

Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?

I would strongly recommend that you NOT store the next inspection date in
tblBMP or in any other table, unless you want the calculated date to be just a
default which can be edited. To display it on a form just use

=DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

as the Control Source of a textbox on the form.

If you have a good reason to actually store it in the table, post back - it's
possible with a bit of VBA code, but it's just likely to be a bad idea to do
so.

John W. Vinson [MVP]

Still no dice on the year addition.. Is this because I am trying to
add a Number field to a Date field? I've double checked the names of
my tables and fields, and everything is correct - except the text box
on my form always shows #Name?. What is causing this error, and is
there a way I can fix it?

Thanks again!

 

[John W. Vinson]

Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.

Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?

I would strongly recommend that you NOT store the next inspection date in
tblBMP or in any other table, unless you want the calculated date to be just a
default which can be edited. To display it on a form just use

=DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])

as the Control Source of a textbox on the form.

If you have a good reason to actually store it in the table, post back - it's
possible with a bit of VBA code, but it's just likely to be a bad idea to do
so.

John W. Vinson [MVP]

Still no dice on the year addition.. Is this because I am trying to
add a Number field to a Date field? I've double checked the names of
my tables and fields, and everything is correct - except the text box
on my form always shows #Name?. What is causing this error, and is
there a way I can fix it?

Thanks again!

If you're using tblBmp in the Control Source of a form control, it probably
won't work - you can only reference fields that are part of the Form's
Recordsource.

What is the actual expression you're using on the form control? Copy and paste
it to a message here, please. Also copy and paste the Form's Recordsource SQL
if it's a query, or the name of the table and the relevant fields if it's a
Table.

John W. Vinson [MVP]

 

[stephen.belyea]

On 31 May 2007 10:36:34 -0700, (e-mail address removed) wrote:
Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.
Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?
I would strongly recommend that you NOT store the next inspection date in
tblBMP or in any other table, unless you want the calculated date to be just a
default which can be edited. To display it on a form just use
=DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
as the Control Source of a textbox on the form.
If you have a good reason to actually store it in the table, post back - it's
possible with a bit of VBA code, but it's just likely to be a bad idea to do
so.
John W. Vinson [MVP]

Still no dice on the year addition.. Is this because I am trying to
add a Number field to a Date field? I've double checked the names of
my tables and fields, and everything is correct - except the text box
on my form always shows #Name?. What is causing this error, and is
there a way I can fix it?

Thanks again!

If you're using tblBmp in the Control Source of a form control, it probably
won't work - you can only reference fields that are part of the Form's
Recordsource.

What is the actual expression you're using on the form control? Copy and paste
it to a message here, please. Also copy and paste the Form's Recordsource SQL
if it's a query, or the name of the table and the relevant fields if it's a
Table.

John W. Vinson [MVP]

This is the expression I am trying to use:

=DateAdd("yyyy",[tblProperty].[PropInspInterval],
[tblInspection.InspCompleted])

I have a form created from tblProperty, which has a subform created
from tblInspection. The only field being used from tblInspection is
the inspection completed date (which I guess leads me to another
question - how to make sure that it calculates the next inspection
date from the most recent completed inspection date). The actual
expression is being run in the tblProperty form, where
tblProperty.PropInspInterval is located (my expression).

 

[stephen.belyea]

On May 31, 8:44 pm, John W. Vinson
On 31 May 2007 10:36:34 -0700, (e-mail address removed) wrote:
Karl and John, I appreciate the assistance. I'm sorry it's taken me so
long to get back to you. I've been out in the field the past few days
with our GPS equipment.
Is it possible to have this field that automatically calculates the
next inspection date as a field in [tblBMP]? How would I get this
argument (to calculate the new date) to appear on a form?
I would strongly recommend that you NOT store the next inspection date in
tblBMP or in any other table, unless you want the calculated date to be just a
default which can be edited. To display it on a form just use
=DateAdd("yyyy", [tblBmp].[InspPeriod], [tblBmp].[InspDate])
as the Control Source of a textbox on the form.
If you have a good reason to actually store it in the table, post back - it's
possible with a bit of VBA code, but it's just likely to be a bad idea to do
so.
John W. Vinson [MVP]
Still no dice on the year addition.. Is this because I am trying to
add a Number field to a Date field? I've double checked the names of
my tables and fields, and everything is correct - except the text box
on my form always shows #Name?. What is causing this error, and is
there a way I can fix it?
Thanks again!

If you're using tblBmp in the Control Source of a form control, it probably
won't work - you can only reference fields that are part of the Form's
Recordsource.

What is the actual expression you're using on the form control? Copy and paste
it to a message here, please. Also copy and paste the Form's Recordsource SQL
if it's a query, or the name of the table and the relevant fields if it's a
Table.

John W. Vinson [MVP]

This is the expression I am trying to use:

=DateAdd("yyyy",[tblProperty].[PropInspInterval],
[tblInspection.InspCompleted])

I have a form created from tblProperty, which has a subform created
from tblInspection. The only field being used from tblInspection is
the inspection completed date (which I guess leads me to another
question - how to make sure that it calculates the next inspection
date from the most recent completed inspection date). The actual
expression is being run in the tblProperty form, where
tblProperty.PropInspInterval is located (my expression).

I've tried searching the news groups for a similar problem, but I
haven't encountered any that are trying to add a value from one table
to a date in another.

 

[John W. Vinson]

I've tried searching the news groups for a similar problem, but I
haven't encountered any that are trying to add a value from one table
to a date in another.

You'ld use an Update query updating the date field to

DateAdd("yyyy", [onetable].[numberfield], [othertable].[datefield])

in a query joining the appropriate records.

Since you haven't posted descriptions of your tables, nor any indication of
how you want to determine which number you want to add to which date, it's a
bit hard to be specific.

John W. Vinson [MVP]

 

[stephen.belyea]

I've tried searching the news groups for a similar problem, but I
haven't encountered any that are trying to add a value from one table
to a date in another.

You'ld use an Update query updating the date field to

DateAdd("yyyy", [onetable].[numberfield], [othertable].[datefield])

in a query joining the appropriate records.

Since you haven't posted descriptions of your tables, nor any indication of
how you want to determine which number you want to add to which date, it's a
bit hard to be specific.

John W. Vinson [MVP]

Oops! Sorry! Let me try to clarify my situation. Within tblInspection
is the date that the BMP was last inspected in a short date format.
This is my tblInspection.InspCompleted. For each Property that has a
BMP on it, we have a maintenance agreement that tells us that each BMP
must be inspected every 'x' years. This 'x' years is where I get my
tblProperty.PropInspInterval. Therefore, each property will have a
hand entered value for PropInspInterval, because agreements differ
between BMP types and the years that the agreements were signed (older
agreements have inspection lengths of ten years, newer ones have 3
years, etc.)

tblProperty is displayed in a form in which tblInspection is a subform
(as each property can have multiple inspections done on it).

I'm going to try to figure out how to make this work with an update
query, and I'll get back to you ASAP. Thanks for your help!

 

[stephen.belyea]

You'ld use an Update query updating the date field to

DateAdd("yyyy", [onetable].[numberfield], [othertable].[datefield])

in a query joining the appropriate records.

Since you haven't posted descriptions of your tables, nor any indication of
how you want to determine which number you want to add to which date, it's a
bit hard to be specific.

John W. Vinson [MVP]

Oops! Sorry! Let me try to clarify my situation. Within tblInspection
is the date that the BMP was last inspected in a short date format.
This is my tblInspection.InspCompleted. For each Property that has a
BMP on it, we have a maintenance agreement that tells us that each BMP
must be inspected every 'x' years. This 'x' years is where I get my
tblProperty.PropInspInterval. Therefore, each property will have a
hand entered value for PropInspInterval, because agreements differ
between BMP types and the years that the agreements were signed (older
agreements have inspection lengths of ten years, newer ones have 3
years, etc.)

tblProperty is displayed in a form in which tblInspection is a subform
(as each property can have multiple inspections done on it).

I'm going to try to figure out how to make this work with an update
query, and I'll get back to you ASAP. Thanks for your help!

I'm not sure how to approach this, creating an update query as a
record source for a text box (I'm assuming that's still the method I
should use). Can someone help me with this?

 

[John W. Vinson]

I'm not sure how to approach this, creating an update query as a
record source for a text box (I'm assuming that's still the method I
should use). Can someone help me with this?

You don't NEED an update query (and you can't use an Update query as the
recordsource for anything anyway).

The next inspection date does not need to be - and should not be - stored in
any table at all.

Instead, you can set the Control Source of a textbox on your form to

=DateAdd("yyyy", [PropInspInterval], [InspCompleted])

This expression will dynamically calculate the next inspection date based on
the interval and the last date.

John W. Vinson [MVP]

 

[stephen.belyea]

I'm not sure how to approach this, creating an update query as a
record source for a text box (I'm assuming that's still the method I
should use). Can someone help me with this?

You don't NEED an update query (and you can't use an Update query as the
recordsource for anything anyway).

The next inspection date does not need to be - and should not be - stored in
any table at all.

Instead, you can set the Control Source of a textbox on your form to

=DateAdd("yyyy", [PropInspInterval], [InspCompleted])

This expression will dynamically calculate the next inspection date based on
the interval and the last date.

John W. Vinson [MVP]

John, I cut and pasted your exact code into the control source of a
textbox. I'm still, however, getting a #NAME? error. Could this be due
to:

+ having tblInspection.InspCompleted as a field in a subform of
tblProperty.PropInspInterval (which is in turn a subform of tblBMP)
+ Null records?

or anything else? While this feature isn't essential to have, it would
greatly improve workflow and reduce user error in calculating times
for inspections.

 

[John W. Vinson]

John, I cut and pasted your exact code into the control source of a
textbox. I'm still, however, getting a #NAME? error. Could this be due
to:

+ having tblInspection.InspCompleted as a field in a subform of
tblProperty.PropInspInterval (which is in turn a subform of tblBMP)
+ Null records?

or anything else? While this feature isn't essential to have, it would
greatly improve workflow and reduce user error in calculating times
for inspections.

It could be due to either. My suggestion was that you put that into a Query as
a calculated field, a query containing the two fields.

John W. Vinson [MVP]