The user is required to enter values in these fields, so the
expressions
will
calculate. There are no null values in the fields for the expressions.
Also, I added in the query about making the expression fields not null
(see
allen's suggestion).
The most recent sql is:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST
COMPLETED DATE])) AS Expr2
FROM MAIN
WHERE (((MAIN.[FREQ INTERVAL]) Is Not Null) AND ((MAIN.[FREQ NUMBER])
Is
Not
Null) AND ((MAIN.[CATEGORY INTERVAL]) Is Not Null) AND ((MAIN.[CATEGORY
NUMBER]) Is Not Null));
Like I said, it works every time I run it from the queries and all the
records display with the calculated expressions. I just can't get it
on
the
report. Here is the SQL of the query that is working. The only
difference
between these 2 queries is that the working one is asking for a section
name.
working query sql:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE], MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL], MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],[Expr1]) AS Expr2
FROM MAIN
WHERE (((MAIN.DEPT) Like [Enter Section]));
Thanks,
Carol
:
And your most recent SQL is what?
Is there a possibility of Null values in your data?
--
Duane Hookom
MS Access MVP
--
Yes I tried the SQL. The queries are working correctly each time I
run
them.
When I try to run the report based on the query - 3 of the 4 give me
an
error. The very 1st one I created has no problems at all.
Carol
:
Did you try Allen's suggestion regarding "using a calculated field
in
another calcuated field"?
Does this SQL work?
PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE],
MAIN.[EST TIME (HR)], MAIN.[LAST COMPLETED DATE],
DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE]) AS
Expr1,
MAIN.[RESPONSIBLE PARTY], MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ,
MAIN.[FREQ INTERVAL], MAIN.[FREQ NUMBER], MAIN.[CATEGORY INTERVAL],
MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY INTERVAL], [CATEGORY NUMBER],
DateAdd([FREQ INTERVAL],[FREQ NUMBER],[LAST COMPLETED DATE])) AS
Expr2
FROM MAIN;
--
Duane Hookom
MS Access MVP
--
Allen,
I'm having trouble understanding where the problem lies. The
queries
work
correctly EACH TIME I run them (there are a total of 4 , with a
report
based
of each one). And both expressions calculate correctly on every
record.
Another weird thing is that the 1st report works correctly off
it's
query.
The subsequent reports do not. I did not copy and paste the 1st
report.
I
created each one using the design wizard.
Any other ideas?
:
The error message indicates that Access does not understand the
query
statement for some reason. The fact that it works sometimes and
not
others
means that the failure to understand is intermittent.
You have declared your 2 parameters. That's great: Access cannot
misunderstand them.
There are two calculated fields, so my first guess is that these
are
where
Access is having the problem.
The first issue is that the first 2 arguments of DateAdd() are
required.
You
therefore need:
WHERE (([FREQ INTERVAL] Is Not Null)
AND ([FREQ NUMBER] Is Not Null)
AND ([CATEGORY INTERVAL] Is Not Null)
AND ([CATEGORY NUMBER] Is Not Null))
Next, JET is not good at understanding the intended data type of
calculated
fields. Even though you would expect it to understand the output
of
DateAdd(), our experience is that it needs to by typecast, i.e.
use:
CVDate(DateAdd([FREQ INTERVAL], [FREQ NUMBER], [LAST
COMPLETED
DATE]))
AS Expr1
More on this issue in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
Finally, using a calculated field in another calcuated field
works
sometimes, so if you are still having problems at this point,
try
replacing
Expr1 in the last field with a repeat of the same expression,
i.e.:
CVDate(DateAdd([CATEGORY INTERVAL], [CATEGORY NUMBER],
CVDate(DateAdd([FREQ INTERVAL], [FREQ NUMBER], [LAST
COMPLETED
DATE]))
)) AS Expr2
That should unconfuse JET for you.
If the report still has problems after that, it is most likely a
Name
AutoCorrect issue where it is thoroughly confused about what
name
applies
to
what. More on this huge issue in:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
I have a report based on a query with 2 separate expressions
contained
within. When I run the query the expressions run fine and I
get
the
data
requested. When I run the report, I'm getting the error that
the
expression
is typed incorrectly or too complex to be evaulated.
The query SQL is:
PARAMETERS [Enter Start Date] DateTime, [Enter End Date]
DateTime;
SELECT MAIN.ID, MAIN.DEPT, MAIN.TASK, MAIN.[PROF MAIL DATE],
MAIN.[EST
TIME
(HR)], MAIN.[LAST COMPLETED DATE], DateAdd([FREQ
INTERVAL],[FREQ
NUMBER],[LAST COMPLETED DATE]) AS Expr1, MAIN.[RESPONSIBLE
PARTY],
MAIN.CATEGORY, MAIN.Notes, MAIN.FREQ, MAIN.[FREQ INTERVAL],
MAIN.[FREQ
NUMBER], MAIN.[CATEGORY INTERVAL], MAIN.[CATEGORY NUMBER],
DateAdd([CATEGORY
INTERVAL],[CATEGORY NUMBER],[Expr1]) AS Expr2
FROM MAIN;
Any idea why the query would run from the query screen and not
from
a
report
based on the query?
Thanks,
Carol
