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. Thanks very much for your
patience.
Marshall Barton said:
It sure sounds as if you are on the right track, but to
eliminate any possibility of confusion, let's switch your
query to SQL view where you can Copy/Paste the query into a
message so I can see exactly what you have.
I expect the query's Where clause to be:
WHERE DateAdd("m", [Service Frequency], [Service Date])
Between [Forms]![Calibration Date Due Dialog]![StartDate]
And [Forms]![Calibration Date Due Dialog]![EndDate]
I don't think you need to worry about the Is Not Null part,
I think it's redundant in this case.
Sorry Marsh. Yes my post doesn’t quite make sense anymore, so I’ll start from
the beginning to rectify any confusion. Forget any previous info. I will try
to be succinct.
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 thought that by
doing this I could use the formula: DateAdd("m",[Service Frequency],[Service
Date]) in queries or straight in the report (whatever works) 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].
As I have previously said, 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?
:
Let's leave the report aside for now and just get the query
working using the query design window.
I think maybe your criteria is not what you really want. I
don't know what you do want, but its probably more than the
data for a single day. Perhaps the criteria should be:
Between [Start date] And [End date]
For all our sakes, please use a shorter field name without
any spaces or other funky characters.
Emily wrote:
I tried what you suggested and put the following expression in the 'field'
of the query
"Next recommended Calibration Due Date: DateAdd("m",[Service
Frequency],[Calibration Date])"
and 'Between [due date] and [due date] in the 'criteria' section
BUT
I now get nothing from the query.
I need to be able to run a report that gives me -for eg -all the tools that
are have a recommended due calibration date in February and therfore I need
to use the between date criteria.
I'm not sure what else I should try...
Emily wrote:
I have a "date serviced" field and i want to auto populate a "next
service date" field by adding 6 months or 12 months from the
original "date serviced".
I used a calculated "unbound" text control containing
= DateAdd("m", [ServiceFrequency], [DateServiced]),
named NextServiceDate for this date to show on reports which it does.
However, 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 original
field that did not populate itself "old service due date". 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 in the query, and therefore not
generate the report I want....
:
Just put the next service date as a calculated field in the
query instead of in the report. Then the report can display
it without using an expression and you can apply your
criteria to the calculated field.
