using option group to auto populate a date field?

[Guest]

nHi,
I have a "date serviced" field and i want to auto populate a "next
recommended service date" field by adding 6 months or 12 months from the
original "date serviced". I though by having an option group the user can
choose either the 6 month button or 12 month button and magically the "next
recommended" field will be auto populated. I've got as far as the option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily

 

[Al Camp]

Emily,
What would happen if you had some service item (say a machine) that would
require service every 4 months, or 3months? You would have to return to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12 or 4 or
whatever), then that should be saved in your table when the machine is first
entered into the system.
A field like ServiceFrequency should contain a value that represents the
number of months until the next service is due, and each machine could have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate on your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced "on the
fly" from the saved ServiceDate and ServiceFrequency.

 

[Guest]

Thanks that Al. It did work. Just one problem though.

I run a report via a dialogue box where I enter 2 dates - a start date and
end date. between these 2 dates are all tools that have a "service due date"
in that time frame.
I use a query to run the report and input the criteria into the origianl
field that do not ppulate itself. However, if there is formula going into an
unbound text box then I cannot put the criteria (for my start and end date)
against any field, and therefore not generate the report I want....

Any advice most welcome.

Emily,
What would happen if you had some service item (say a machine) that would
require service every 4 months, or 3months? You would have to return to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12 or 4 or
whatever), then that should be saved in your table when the machine is first
entered into the system.
A field like ServiceFrequency should contain a value that represents the
number of months until the next service is due, and each machine could have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate on your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced "on the
fly" from the saved ServiceDate and ServiceFrequency.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nHi,
I have a "date serviced" field and i want to auto populate a "next
recommended service date" field by adding 6 months or 12 months from the
original "date serviced". I though by having an option group the user can
choose either the 6 month button or 12 month button and magically the
"next
recommended" field will be auto populated. I've got as far as the option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily

 

[Al Camp]

Emily,
I don't understand what you mean by...

I use a query to run the report and input the criteria into the origianl
field that do not ppulate itself. However, if there is formula going into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

If my suggestion below is off the mark please use examples and more detail
as to what you need.

This describes a parameter query with a date range, and how those parameter
values can be used by the query and/or report.

In your ServiceDueDate field in the query, use this criteria...
Between [Enter Start Date] and [Enter End Date]
This is a parameter query, and when the query/report is run, the user will
be prompted automatically for a StartDate value and an EndDate value. But
those values are assigned to [Enter Start Date] and [Enter End Date] just as
though it was a variable.

Now, in your query or in your report, you can use [Enter Start Date] and
[Enter End Date] just like a field value.
For example, an unbound text control in your report header with a
ControlSource of...
= "From " & [Enter Start Date] & " to " & [Enter End Date]
would yield...
"From 1/1/06 to 2/1/06"

Thanks that Al. It did work. Just one problem though.

I run a report via a dialogue box where I enter 2 dates - a start date and
end date. between these 2 dates are all tools that have a "service due
date"
in that time frame.
I use a query to run the report and input the criteria into the origianl
field that do not ppulate itself. However, if there is formula going into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

Any advice most welcome.

Emily,
What would happen if you had some service item (say a machine) that
would
require service every 4 months, or 3months? You would have to return to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12 or 4 or
whatever), then that should be saved in your table when the machine is
first
entered into the system.
A field like ServiceFrequency should contain a value that represents
the
number of months until the next service is due, and each machine could
have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate on
your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced "on
the
fly" from the saved ServiceDate and ServiceFrequency.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nHi,
I have a "date serviced" field and i want to auto populate a "next
recommended service date" field by adding 6 months or 12 months from
the
original "date serviced". I though by having an option group the user
can
choose either the 6 month button or 12 month button and magically the
"next
recommended" field will be auto populated. I've got as far as the
option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily

 

[Guest]

I think we are both missing each others point. And it' probably due to my
poor explanation due to being up too late. I'll start fresh.....

This is a database that registers tool service details.

When entering data I want the user to be able to choose when the tool is due
for its next service ie in 3 months, 6 months or 12 months etc. I added a
field to the table which the form is based on called [service frequency] so
the user can put any number of months into this field. I used the formula:
DateAdd("m",[Service Frequency],[Service Date]) in the query field and
straight in the report so that the number of months will automatically be
added on to the [Service Date].

I then want to run a report so that the user can see what tools are due for
service –for eg in February. So they will enter dates 01/02/06 and 28/02/06.
I have a dialog form to enter these dates into and would use the following
criteria:
Is Not Null And Between [Forms]![Calibration Date Due Dialog]![StartDate]
And [Forms]![Calibration Date Due Dialog]![EndDate].

I have used the DateAdd formula in a field in the query with the criteria
above and also tried the Between [Start Date] and [End Date] criteria and
nothing is happening.

So I’m not sure what is wrong, if I’m using the wrong criteria or wrong
formula or both. But I’m very stuck at the moment and don’t know what to do?

Emily,
I don't understand what you mean by...

I use a query to run the report and input the criteria into the origianl
field that do not ppulate itself. However, if there is formula going into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

If my suggestion below is off the mark please use examples and more detail
as to what you need.

This describes a parameter query with a date range, and how those parameter
values can be used by the query and/or report.

In your ServiceDueDate field in the query, use this criteria...
Between [Enter Start Date] and [Enter End Date]
This is a parameter query, and when the query/report is run, the user will
be prompted automatically for a StartDate value and an EndDate value. But
those values are assigned to [Enter Start Date] and [Enter End Date] just as
though it was a variable.

Now, in your query or in your report, you can use [Enter Start Date] and
[Enter End Date] just like a field value.
For example, an unbound text control in your report header with a
ControlSource of...
= "From " & [Enter Start Date] & " to " & [Enter End Date]
would yield...
"From 1/1/06 to 2/1/06"

Thanks that Al. It did work. Just one problem though.

I run a report via a dialogue box where I enter 2 dates - a start date and
end date. between these 2 dates are all tools that have a "service due
date"
in that time frame.
I use a query to run the report and input the criteria into the origianl
field that do not ppulate itself. However, if there is formula going into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

Any advice most welcome.

Emily,
What would happen if you had some service item (say a machine) that
would
require service every 4 months, or 3months? You would have to return to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12 or 4 or
whatever), then that should be saved in your table when the machine is
first
entered into the system.
A field like ServiceFrequency should contain a value that represents
the
number of months until the next service is due, and each machine could
have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate on
your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced "on
the
fly" from the saved ServiceDate and ServiceFrequency.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nHi,
I have a "date serviced" field and i want to auto populate a "next
recommended service date" field by adding 6 months or 12 months from
the
original "date serviced". I though by having an option group the user
can
choose either the 6 month button or 12 month button and magically the
"next
recommended" field will be auto populated. I've got as far as the
option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily

 

[Al Camp]

Emily,
I expect you're using the query grid to design your query for the
report. I'm also
assuming this is not a Totals query, but a normal Select query.

Looks like you're well on the way... we must just be missing something
minor
here. The form and StartDate and EndDate sounds good, adding
ServiceFrequency field is good, your
DateAdd calculation is good, and your criteria appears to be OK.
Let's just run the query for now, let the report go until our query
returns good data.

BUT, when you say you run the query and "nothing happens", what do you
mean
by that? You get no data returned? The query doesn't run at all?

Now... from what you've told me so far, I would expect a calculated
column
in your query like this to work...

In the Field portion of the column...
NextServiceDate : DateAdd("m",[Service Frequency],[Service Date])

In the Criteria of that column... (let's drop the Is Not Null requirement
for
right now and just work on the dates)
Between [Forms]![Calibration Date Due Dialog]![StartDate] And
[Forms]![Calibration Date Due Dialog]![EndDate].

That "should" do it. Hang in there, we'll get it. Email is not the most
efficient way to "bug shoot" these problems... but we have to deal with it.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I think we are both missing each others point. And it' probably due to my
poor explanation due to being up too late. I'll start fresh.....

This is a database that registers tool service details.

When entering data I want the user to be able to choose when the tool is
due
for its next service ie in 3 months, 6 months or 12 months etc. I added a
field to the table which the form is based on called [service frequency]
so
the user can put any number of months into this field. I used the formula:
DateAdd("m",[Service Frequency],[Service Date]) in the query field and
straight in the report so that the number of months will automatically be
added on to the [Service Date].

I then want to run a report so that the user can see what tools are due
for
service -for eg in February. So they will enter dates 01/02/06 and
28/02/06.
I have a dialog form to enter these dates into and would use the following
criteria:
Is Not Null And Between [Forms]![Calibration Date Due Dialog]![StartDate]
And [Forms]![Calibration Date Due Dialog]![EndDate].

I have used the DateAdd formula in a field in the query with the criteria
above and also tried the Between [Start Date] and [End Date] criteria and
nothing is happening.

So I'm not sure what is wrong, if I'm using the wrong criteria or wrong
formula or both. But I'm very stuck at the moment and don't know what to
do?

Emily,
I don't understand what you mean by...

I use a query to run the report and input the criteria into the
origianl
field that do not ppulate itself. However, if there is formula going
into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

If my suggestion below is off the mark please use examples and more
detail
as to what you need.

This describes a parameter query with a date range, and how those
parameter
values can be used by the query and/or report.

In your ServiceDueDate field in the query, use this criteria...
Between [Enter Start Date] and [Enter End Date]
This is a parameter query, and when the query/report is run, the user
will
be prompted automatically for a StartDate value and an EndDate value.
But
those values are assigned to [Enter Start Date] and [Enter End Date] just
as
though it was a variable.

Now, in your query or in your report, you can use [Enter Start Date] and
[Enter End Date] just like a field value.
For example, an unbound text control in your report header with a
ControlSource of...
= "From " & [Enter Start Date] & " to " & [Enter End Date]
would yield...
"From 1/1/06 to 2/1/06"

Thanks that Al. It did work. Just one problem though.

I run a report via a dialogue box where I enter 2 dates - a start date
and
end date. between these 2 dates are all tools that have a "service due
date"
in that time frame.
I use a query to run the report and input the criteria into the
origianl
field that do not ppulate itself. However, if there is formula going
into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

Any advice most welcome.

:

Emily,
What would happen if you had some service item (say a machine) that
would
require service every 4 months, or 3months? You would have to return
to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12 or 4
or
whatever), then that should be saved in your table when the machine is
first
entered into the system.
A field like ServiceFrequency should contain a value that
represents
the
number of months until the next service is due, and each machine could
have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate on
your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced "on
the
fly" from the saved ServiceDate and ServiceFrequency.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nHi,
I have a "date serviced" field and i want to auto populate a "next
recommended service date" field by adding 6 months or 12 months from
the
original "date serviced". I though by having an option group the
user
can
choose either the 6 month button or 12 month button and magically
the
"next
recommended" field will be auto populated. I've got as far as the
option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily

 

[Guest]

Well now i'm about to do my head in...

yes it is a select query

yes i use the query grid.

I have tried everything you have said. with the show box ticket and
unticked, and I have used an expresion name and no expression name and now I
run the query and i get a message:
This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

This is what is typed into the SQL view:
SELECT tblCustomerDetailsDATAENTRY.[Customer Name],
tbltoolcalibrationDATAENTRY.[Calibration Date],
tbltoolcalibrationDATAENTRY.[Job Number], tblToolDetailsDATAENTRY.[Tool
Description], tblToolDetailsDATAENTRY.Brand, tblToolDetailsDATAENTRY.Model,
DateAdd("m",[ServiceFrequency],[Calibration Date]) AS NextCalDate
FROM tblCustomerDetailsDATAENTRY INNER JOIN (tblToolDetailsDATAENTRY INNER
JOIN tbltoolcalibrationDATAENTRY ON tblToolDetailsDATAENTRY.[Tool ID] =
tbltoolcalibrationDATAENTRY.[Tool ID]) ON
tblCustomerDetailsDATAENTRY.[Customer ID] = tblToolDetailsDATAENTRY.[Customer
ID]
WHERE (((DateAdd("m",[ServiceFrequency],[Calibration Date])) Between
[Forms]![CalibrationDueDialog]![StartDate] And
[Forms]![CalibrationDueDialog]![EndDate]));


I am sure it is not supposed to be this difficult. arghhhhhhhhhhhhhhh

Emily,
I expect you're using the query grid to design your query for the
report. I'm also
assuming this is not a Totals query, but a normal Select query.

Looks like you're well on the way... we must just be missing something
minor
here. The form and StartDate and EndDate sounds good, adding
ServiceFrequency field is good, your
DateAdd calculation is good, and your criteria appears to be OK.
Let's just run the query for now, let the report go until our query
returns good data.

BUT, when you say you run the query and "nothing happens", what do you
mean
by that? You get no data returned? The query doesn't run at all?

Now... from what you've told me so far, I would expect a calculated
column
in your query like this to work...

In the Field portion of the column...
NextServiceDate : DateAdd("m",[Service Frequency],[Service Date])

In the Criteria of that column... (let's drop the Is Not Null requirement
for
right now and just work on the dates)
Between [Forms]![Calibration Date Due Dialog]![StartDate] And
[Forms]![Calibration Date Due Dialog]![EndDate].

That "should" do it. Hang in there, we'll get it. Email is not the most
efficient way to "bug shoot" these problems... but we have to deal with it.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I think we are both missing each others point. And it' probably due to my
poor explanation due to being up too late. I'll start fresh.....

This is a database that registers tool service details.

When entering data I want the user to be able to choose when the tool is
due
for its next service ie in 3 months, 6 months or 12 months etc. I added a
field to the table which the form is based on called [service frequency]
so
the user can put any number of months into this field. I used the formula:
DateAdd("m",[Service Frequency],[Service Date]) in the query field and
straight in the report so that the number of months will automatically be
added on to the [Service Date].

I then want to run a report so that the user can see what tools are due
for
service -for eg in February. So they will enter dates 01/02/06 and
28/02/06.
I have a dialog form to enter these dates into and would use the following
criteria:
Is Not Null And Between [Forms]![Calibration Date Due Dialog]![StartDate]
And [Forms]![Calibration Date Due Dialog]![EndDate].

I have used the DateAdd formula in a field in the query with the criteria
above and also tried the Between [Start Date] and [End Date] criteria and
nothing is happening.

So I'm not sure what is wrong, if I'm using the wrong criteria or wrong
formula or both. But I'm very stuck at the moment and don't know what to
do?

Emily,
I don't understand what you mean by...
I use a query to run the report and input the criteria into the
origianl
field that do not ppulate itself. However, if there is formula going
into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....
If my suggestion below is off the mark please use examples and more
detail
as to what you need.

This describes a parameter query with a date range, and how those
parameter
values can be used by the query and/or report.

In your ServiceDueDate field in the query, use this criteria...
Between [Enter Start Date] and [Enter End Date]
This is a parameter query, and when the query/report is run, the user
will
be prompted automatically for a StartDate value and an EndDate value.
But
those values are assigned to [Enter Start Date] and [Enter End Date] just
as
though it was a variable.

Now, in your query or in your report, you can use [Enter Start Date] and
[Enter End Date] just like a field value.
For example, an unbound text control in your report header with a
ControlSource of...
= "From " & [Enter Start Date] & " to " & [Enter End Date]
would yield...
"From 1/1/06 to 2/1/06"

Thanks that Al. It did work. Just one problem though.

I run a report via a dialogue box where I enter 2 dates - a start date
and
end date. between these 2 dates are all tools that have a "service due
date"
in that time frame.
I use a query to run the report and input the criteria into the
origianl
field that do not ppulate itself. However, if there is formula going
into
an
unbound text box then I cannot put the criteria (for my start and end
date)
against any field, and therefore not generate the report I want....

Any advice most welcome.

:

Emily,
What would happen if you had some service item (say a machine) that
would
require service every 4 months, or 3months? You would have to return
to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12 or 4
or
whatever), then that should be saved in your table when the machine is
first
entered into the system.
A field like ServiceFrequency should contain a value that
represents
the
number of months until the next service is due, and each machine could
have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate on
your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced "on
the
fly" from the saved ServiceDate and ServiceFrequency.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nHi,
I have a "date serviced" field and i want to auto populate a "next
recommended service date" field by adding 6 months or 12 months from
the
original "date serviced". I though by having an option group the
user
can
choose either the 6 month button or 12 month button and magically
the
"next
recommended" field will be auto populated. I've got as far as the
option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily

 

[Al Camp]

Emily,
You have to do your own problem determination here. Remotely, I can't
see your table setups, relationships, form fields, query fields, and the
host of other factors that might be causing the problem.
It appears as though you've introduced another problem into mix, and we
must find the source of that problem before we go on to the criteria
problem.

If you remove JUST the date criteria from the NextCalDate column, does
the query run, (returning ALL records)?

If not, then... remove the NextCalDate column itself. Does it run now?

Keep removing any suspect fields/calculations until the query runs clean,
then start adding them back. That way you can return the culprit that's
causing the Too Complex error.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Well now i'm about to do my head in...

yes it is a select query

yes i use the query grid.

I have tried everything you have said. with the show box ticket and
unticked, and I have used an expresion name and no expression name and now
I
run the query and i get a message:
This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

This is what is typed into the SQL view:
SELECT tblCustomerDetailsDATAENTRY.[Customer Name],
tbltoolcalibrationDATAENTRY.[Calibration Date],
tbltoolcalibrationDATAENTRY.[Job Number], tblToolDetailsDATAENTRY.[Tool
Description], tblToolDetailsDATAENTRY.Brand,
tblToolDetailsDATAENTRY.Model,
DateAdd("m",[ServiceFrequency],[Calibration Date]) AS NextCalDate
FROM tblCustomerDetailsDATAENTRY INNER JOIN (tblToolDetailsDATAENTRY INNER
JOIN tbltoolcalibrationDATAENTRY ON tblToolDetailsDATAENTRY.[Tool ID] =
tbltoolcalibrationDATAENTRY.[Tool ID]) ON
tblCustomerDetailsDATAENTRY.[Customer ID] =
tblToolDetailsDATAENTRY.[Customer
ID]
WHERE (((DateAdd("m",[ServiceFrequency],[Calibration Date])) Between
[Forms]![CalibrationDueDialog]![StartDate] And
[Forms]![CalibrationDueDialog]![EndDate]));


I am sure it is not supposed to be this difficult. arghhhhhhhhhhhhhhh

Emily,
I expect you're using the query grid to design your query for the
report. I'm also
assuming this is not a Totals query, but a normal Select query.

Looks like you're well on the way... we must just be missing
something
minor
here. The form and StartDate and EndDate sounds good, adding
ServiceFrequency field is good, your
DateAdd calculation is good, and your criteria appears to be OK.
Let's just run the query for now, let the report go until our query
returns good data.

BUT, when you say you run the query and "nothing happens", what do
you
mean
by that? You get no data returned? The query doesn't run at all?

Now... from what you've told me so far, I would expect a calculated
column
in your query like this to work...

In the Field portion of the column...
NextServiceDate : DateAdd("m",[Service Frequency],[Service Date])

In the Criteria of that column... (let's drop the Is Not Null
requirement
for
right now and just work on the dates)
Between [Forms]![Calibration Date Due Dialog]![StartDate] And
[Forms]![Calibration Date Due Dialog]![EndDate].

That "should" do it. Hang in there, we'll get it. Email is not the
most
efficient way to "bug shoot" these problems... but we have to deal with
it.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I think we are both missing each others point. And it' probably due to
my
poor explanation due to being up too late. I'll start fresh.....

This is a database that registers tool service details.

When entering data I want the user to be able to choose when the tool
is
due
for its next service ie in 3 months, 6 months or 12 months etc. I added
a
field to the table which the form is based on called [service
frequency]
so
the user can put any number of months into this field. I used the
formula:
DateAdd("m",[Service Frequency],[Service Date]) in the query field and
straight in the report so that the number of months will automatically
be
added on to the [Service Date].

I then want to run a report so that the user can see what tools are due
for
service -for eg in February. So they will enter dates 01/02/06 and
28/02/06.
I have a dialog form to enter these dates into and would use the
following
criteria:
Is Not Null And Between [Forms]![Calibration Date Due
Dialog]![StartDate]
And [Forms]![Calibration Date Due Dialog]![EndDate].

I have used the DateAdd formula in a field in the query with the
criteria
above and also tried the Between [Start Date] and [End Date] criteria
and
nothing is happening.

So I'm not sure what is wrong, if I'm using the wrong criteria or wrong
formula or both. But I'm very stuck at the moment and don't know what
to
do?

:

Emily,
I don't understand what you mean by...
I use a query to run the report and input the criteria into the
origianl
field that do not ppulate itself. However, if there is formula going
into
an
unbound text box then I cannot put the criteria (for my start and
end
date)
against any field, and therefore not generate the report I want....
If my suggestion below is off the mark please use examples and more
detail
as to what you need.

This describes a parameter query with a date range, and how those
parameter
values can be used by the query and/or report.

In your ServiceDueDate field in the query, use this criteria...
Between [Enter Start Date] and [Enter End Date]
This is a parameter query, and when the query/report is run, the user
will
be prompted automatically for a StartDate value and an EndDate value.
But
those values are assigned to [Enter Start Date] and [Enter End Date]
just
as
though it was a variable.

Now, in your query or in your report, you can use [Enter Start Date]
and
[Enter End Date] just like a field value.
For example, an unbound text control in your report header with a
ControlSource of...
= "From " & [Enter Start Date] & " to " & [Enter End Date]
would yield...
"From 1/1/06 to 2/1/06"

Thanks that Al. It did work. Just one problem though.

I run a report via a dialogue box where I enter 2 dates - a start
date
and
end date. between these 2 dates are all tools that have a "service
due
date"
in that time frame.
I use a query to run the report and input the criteria into the
origianl
field that do not ppulate itself. However, if there is formula going
into
an
unbound text box then I cannot put the criteria (for my start and
end
date)
against any field, and therefore not generate the report I want....

Any advice most welcome.

:

Emily,
What would happen if you had some service item (say a machine)
that
would
require service every 4 months, or 3months? You would have to
return
to
your form design and add another option for that.

Try this instead...
If each service item has a preset service requirement (6 or 12
or 4
or
whatever), then that should be saved in your table when the machine
is
first
entered into the system.
A field like ServiceFrequency should contain a value that
represents
the
number of months until the next service is due, and each machine
could
have
it's own unique value.

Now, a calculated "unbound" text control, named NextServiceDate
on
your
form, (or in any subsequent form, query, or report) with...
= DateAdd("m", [ServiceFrequency], [DateServiced]
will always "display" the next service date.

No need to save the NextServiceDate. It can always be recalced
"on
the
fly" from the saved ServiceDate and ServiceFrequency.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

nHi,
I have a "date serviced" field and i want to auto populate a
"next
recommended service date" field by adding 6 months or 12 months
from
the
original "date serviced". I though by having an option group the
user
can
choose either the 6 month button or 12 month button and magically
the
"next
recommended" field will be auto populated. I've got as far as the
option
group but do not know what to do next or what code to use where.

Or maybe my idea is wrong altogether. Please advise.

Please help!
Emily