Help with the DateAdd function

[Carol]

I am using the dateadd function to add an interval to a specific date.
However, this is adding the same interval to all records in the database. In
this database, each record can have a different interval (annual - "yyyy",1
monthly- "m",1 quarterly-"m", 3 etc.)

I created fields to enter the interval ("m") and number (1) for each record.
How do I reference these fields in the expression. I used the following:
Field names - INTERVAL, NUMBER, LAST COMPLETED DATE

=Dateadd([INTERVAL],[NUMBER],[LAST COMPLETED DATE])

I am getting an error. Any help would be appreciated.
Thanks,
Carol

 

[Douglas J. Steele]

What's the error?

 

[John Vinson]

I am using the dateadd function to add an interval to a specific date.
However, this is adding the same interval to all records in the database. In
this database, each record can have a different interval (annual - "yyyy",1
monthly- "m",1 quarterly-"m", 3 etc.)
I created fields to enter the interval ("m") and number (1) for each record.
How do I reference these fields in the expression. I used the following:
Field names - INTERVAL, NUMBER, LAST COMPLETED DATE

=Dateadd([INTERVAL],[NUMBER],[LAST COMPLETED DATE])

I am getting an error. Any help would be appreciated.

Are you trying to *store* the result? or just calculate it in a Query
(the latter would be the correct method)? Where are you putting this
expression?

John W. Vinson[MVP]

 

[Carol]

The calculated field is in the form used to enter the data. When I enter the
interval, number and last completed date in their fields, the calculated
field returns #Error

What's the error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using the dateadd function to add an interval to a specific date.
However, this is adding the same interval to all records in the database.
In
this database, each record can have a different interval (annual -
"yyyy",1
monthly- "m",1 quarterly-"m", 3 etc.)

I created fields to enter the interval ("m") and number (1) for each
record.
How do I reference these fields in the expression. I used the following:
Field names - INTERVAL, NUMBER, LAST COMPLETED DATE

=Dateadd([INTERVAL],[NUMBER],[LAST COMPLETED DATE])

I am getting an error. Any help would be appreciated.
Thanks,
Carol

 

[Carol]

I put the expression in a calculated field on a form to enter the data. The
form was based on the main data table, not a query.

The calculated date doesn't need to be stored indefinitely, but it does need
to appear on reports.

I am using the dateadd function to add an interval to a specific date.
However, this is adding the same interval to all records in the database. In
this database, each record can have a different interval (annual - "yyyy",1
monthly- "m",1 quarterly-"m", 3 etc.)
I created fields to enter the interval ("m") and number (1) for each record.
How do I reference these fields in the expression. I used the following:
Field names - INTERVAL, NUMBER, LAST COMPLETED DATE

=Dateadd([INTERVAL],[NUMBER],[LAST COMPLETED DATE])

I am getting an error. Any help would be appreciated.

Are you trying to *store* the result? or just calculate it in a Query
(the latter would be the correct method)? Where are you putting this
expression?

John W. Vinson[MVP]

 

[John Vinson]

I put the expression in a calculated field on a form to enter the data. The
form was based on the main data table, not a query.

You're still getting #Error? What is the EXACT ControlSource property
of the Textbox on the form which is showing the error message? What
are the fieldnames in the table, and the other control name on the
form?

The calculated date doesn't need to be stored indefinitely, but it does need
to appear on reports.

Just use the same Control Source for a textbox on the report (once
it's working correctly of course!)

John W. Vinson[MVP]

 

[Carol]

The control source for the text box showing the error is:
=DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])

The are alot of fields ont he form, but for the expression, the names are :
FREQ INTERVAL, FREQ NUMBER & LAST COMPLETED DATE.

Sorry, but I'm a bit of a novice, when you ask for the other control name of
the form, what do you mean?

Carol

 

[George Nicholson]

I would check that you are actually passing DateAdd the information you
think you are (i.e., "m", 1 and a Date).

In the text box where you currently have the DateAdd function, test *each*
of your three arguments individually by changing the control source to a
single value ([FREQ INTERVAL], [FREQ NUMBER] or [LAST COMPLETED DATE]), and
looking at the result in form view after each change.

I played around with this in a form, feeding DateAdd "bad" arguments and the
only times I could generate an #Error is if the Interval argument was
invalid (like "b" for Interval, not one of the options), or "test" rather
than a number for Interval#. Hopefully, if you check them one-by-one, you
will find the stumbling block.

If fields names were mispelled or unknown you would be getting a #Name
error, so the Field/Control names pass. It is whatever is *in* those
fields/controls that is causing the problem.

(Any chance FreqInterval actually contains the number argument and
FreqNumber contains the Interval string? i.e., are they reversed?)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

The control source for the text box showing the error is:
=DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])

The are alot of fields ont he form, but for the expression, the names are
:
FREQ INTERVAL, FREQ NUMBER & LAST COMPLETED DATE.

Sorry, but I'm a bit of a novice, when you ask for the other control name
of
the form, what do you mean?

Carol

You're still getting #Error? What is the EXACT ControlSource property
of the Textbox on the form which is showing the error message? What
are the fieldnames in the table, and the other control name on the
form?


Just use the same Control Source for a textbox on the report (once
it's working correctly of course!)

John W. Vinson[MVP]

 

[Carol]

George,
thanks for the help. The field names were spelled correctly and were in the
right order, but I found that the FREQ INTERVAL field was the problem. I
entered the interval in quotes (e.g. "yyyy"). When I removed the quotes from
this field, the expression worked.
Thanks,
Carol

I would check that you are actually passing DateAdd the information you
think you are (i.e., "m", 1 and a Date).

In the text box where you currently have the DateAdd function, test *each*
of your three arguments individually by changing the control source to a
single value ([FREQ INTERVAL], [FREQ NUMBER] or [LAST COMPLETED DATE]), and
looking at the result in form view after each change.

I played around with this in a form, feeding DateAdd "bad" arguments and the
only times I could generate an #Error is if the Interval argument was
invalid (like "b" for Interval, not one of the options), or "test" rather
than a number for Interval#. Hopefully, if you check them one-by-one, you
will find the stumbling block.

If fields names were mispelled or unknown you would be getting a #Name
error, so the Field/Control names pass. It is whatever is *in* those
fields/controls that is causing the problem.

(Any chance FreqInterval actually contains the number argument and
FreqNumber contains the Interval string? i.e., are they reversed?)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

The control source for the text box showing the error is:
=DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])

The are alot of fields ont he form, but for the expression, the names are
:
FREQ INTERVAL, FREQ NUMBER & LAST COMPLETED DATE.

Sorry, but I'm a bit of a novice, when you ask for the other control name
of
the form, what do you mean?

Carol

I put the expression in a calculated field on a form to enter the data.
The
form was based on the main data table, not a query.

You're still getting #Error? What is the EXACT ControlSource property
of the Textbox on the form which is showing the error message? What
are the fieldnames in the table, and the other control name on the
form?

The calculated date doesn't need to be stored indefinitely, but it does
need
to appear on reports.

Just use the same Control Source for a textbox on the report (once
it's working correctly of course!)

John W. Vinson[MVP]

 

[George Nicholson]

glad we were able to help

--
George Nicholson

Remove 'Junk' from return address.

George,
thanks for the help. The field names were spelled correctly and were in
the
right order, but I found that the FREQ INTERVAL field was the problem. I
entered the interval in quotes (e.g. "yyyy"). When I removed the quotes
from
this field, the expression worked.
Thanks,
Carol

I would check that you are actually passing DateAdd the information you
think you are (i.e., "m", 1 and a Date).

In the text box where you currently have the DateAdd function, test
*each*
of your three arguments individually by changing the control source to a
single value ([FREQ INTERVAL], [FREQ NUMBER] or [LAST COMPLETED DATE]),
and
looking at the result in form view after each change.

I played around with this in a form, feeding DateAdd "bad" arguments and
the
only times I could generate an #Error is if the Interval argument was
invalid (like "b" for Interval, not one of the options), or "test" rather
than a number for Interval#. Hopefully, if you check them one-by-one, you
will find the stumbling block.

If fields names were mispelled or unknown you would be getting a #Name
error, so the Field/Control names pass. It is whatever is *in* those
fields/controls that is causing the problem.

(Any chance FreqInterval actually contains the number argument and
FreqNumber contains the Interval string? i.e., are they reversed?)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

The control source for the text box showing the error is:
=DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])

The are alot of fields ont he form, but for the expression, the names
are
:
FREQ INTERVAL, FREQ NUMBER & LAST COMPLETED DATE.

Sorry, but I'm a bit of a novice, when you ask for the other control
name
of
the form, what do you mean?

Carol



:


I put the expression in a calculated field on a form to enter the
data.
The
form was based on the main data table, not a query.

You're still getting #Error? What is the EXACT ControlSource property
of the Textbox on the form which is showing the error message? What
are the fieldnames in the table, and the other control name on the
form?

The calculated date doesn't need to be stored indefinitely, but it
does
need
to appear on reports.

Just use the same Control Source for a textbox on the report (once
it's working correctly of course!)

John W. Vinson[MVP]