Date Formulas

[Guest]

I am trying to create a report where I can include the field that shows our
clients last assessment date and then dates at 3 months, 6 months, 9 months
and 12 months, for quarterly followups. I followed the directions in my
"Access 2000 Bible" for Expression Builder and my expressions look just like
they should. However, when I try to run the report, it prompts me with a
querie "Date". I don't know what date it wants and why. My "Assessment
Date" Field is part of the report, so it should not need me to enter anything.
Can anyone help?
Thanks

 

[fredg]

I am trying to create a report where I can include the field that shows our
clients last assessment date and then dates at 3 months, 6 months, 9 months
and 12 months, for quarterly followups. I followed the directions in my
"Access 2000 Bible" for Expression Builder and my expressions look just like
they should. However, when I try to run the report, it prompts me with a
querie "Date". I don't know what date it wants and why. My "Assessment
Date" Field is part of the report, so it should not need me to enter anything.
Can anyone help?
Thanks

How about telling us the actual names of the fields involved and the
exact expressions you are using.

By the way if you are using a field named "Date" ....
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Guest]

Hi,

Assuming that Assessment Date is an actual date/time datatype field, create
four unbound text boxes on your report. In the Control Source for each of
these unbound text boxes, but something like below. Change the 1 to 2, 3, or
4 to get the right quarters.

=DateAdd("q",1,[Assessment Date])

 

[Guest]

O.K., The database is called CARES East Client List and the date field is
called Assess Date.
My expression to get a date 3 months from the Assessment Date is as follows:
Expr1ateAdd("m",3,[CARES East Client List]![Assess Date])

 

[Guest]

That's sort of what I did. See my response to Fred.
--
SusieQ

Hi,

Assuming that Assessment Date is an actual date/time datatype field, create
four unbound text boxes on your report. In the Control Source for each of
these unbound text boxes, but something like below. Change the 1 to 2, 3, or
4 to get the right quarters.

=DateAdd("q",1,[Assessment Date])
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to create a report where I can include the field that shows our
clients last assessment date and then dates at 3 months, 6 months, 9 months
and 12 months, for quarterly followups. I followed the directions in my
"Access 2000 Bible" for Expression Builder and my expressions look just like
they should. However, when I try to run the report, it prompts me with a
querie "Date". I don't know what date it wants and why. My "Assessment
Date" Field is part of the report, so it should not need me to enter anything.
Can anyone help?
Thanks

 

[fredg]

O.K., The database is called CARES East Client List and the date field is
called Assess Date.
My expression to get a date 3 months from the Assessment Date is as follows:
Expr1ateAdd("m",3,[CARES East Client List]![Assess Date])

Your doing this in a query? Not directly in the report?

I assume [CARES East Client List] is the name of the table (not the
name of the 'Database') that the field [Assess Date] is in and it is
included in the Query's record source.

If so, then try:
Expr1ateAdd("m",3,[Assess Date])
Expr2ateAdd("m",6,[Assess Date])
etc.

If my assumption above is incorrect and [CARES East Client List] is
not included in the query's record source, then you will have to use a
DLookUp to find the value to add months to:

Expr1ateAdd("m",3,DLookUp("[Assess Date]","CARES East Client
List","Some additional criteria may be needed here"))

Look up Dlookup in VBA help.
Also look up "Restrict data to a subset of records"

Run the query.
It should give you the dates 3 months, 6 months, etc., from the
[Assess Date], without any prompts.

Run the report. If you then get a prompt to Enter "Date", then that
prompt is from the report, not the query.
You will need to look for a control that is named "Date" as well as a
field named "Date" (which is not a good name for a control or field.
See my previous post.).
A good place to start looking is in the Report's Sorting and Grouping
dialog.
If it isn't there enter an obviously incorrect value, such as
12/2/2222 when prompted, and look for it in the report.

 

[Guest]

Thanks Fred
That worked but I also discovered that I could not use the name Assess Date.
The word Date messed it up. I renamed the field just "Assess" and then
corrected the Expression as well and it worked wonderfully.

My boss is happy with the results and that's what matters.
Thanks
--
SusieQ

O.K., The database is called CARES East Client List and the date field is
called Assess Date.
My expression to get a date 3 months from the Assessment Date is as follows:
Expr1ateAdd("m",3,[CARES East Client List]![Assess Date])

Your doing this in a query? Not directly in the report?

I assume [CARES East Client List] is the name of the table (not the
name of the 'Database') that the field [Assess Date] is in and it is
included in the Query's record source.

If so, then try:
Expr1ateAdd("m",3,[Assess Date])
Expr2ateAdd("m",6,[Assess Date])
etc.

If my assumption above is incorrect and [CARES East Client List] is
not included in the query's record source, then you will have to use a
DLookUp to find the value to add months to:

Expr1ateAdd("m",3,DLookUp("[Assess Date]","CARES East Client
List","Some additional criteria may be needed here"))

Look up Dlookup in VBA help.
Also look up "Restrict data to a subset of records"

Run the query.
It should give you the dates 3 months, 6 months, etc., from the
[Assess Date], without any prompts.

Run the report. If you then get a prompt to Enter "Date", then that
prompt is from the report, not the query.
You will need to look for a control that is named "Date" as well as a
field named "Date" (which is not a good name for a control or field.
See my previous post.).
A good place to start looking is in the Report's Sorting and Grouping
dialog.
If it isn't there enter an obviously incorrect value, such as
12/2/2222 when prompted, and look for it in the report.

 

[John Vinson]

Thanks Fred
That worked but I also discovered that I could not use the name Assess Date.
The word Date messed it up. I renamed the field just "Assess" and then
corrected the Expression as well and it worked wonderfully.

This sounds like some of the mischief that "Name Autocorrect" causes.
I'd suggest turning this badly implemented feature OFF.

John W. Vinson[MVP]