Expression error when running report, but not in query.

C

Carol

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
 
A

Allen Browne

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
 
C

Carol

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?

Allen Browne said:
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.

Carol said:
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
 
D

Duane Hookom

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
--


Carol said:
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?

Allen Browne said:
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.

Carol said:
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
 
C

Carol

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

Duane Hookom said:
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
--


Carol said:
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?

Allen Browne said:
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
 
D

Duane Hookom

And your most recent SQL is what?
Is there a possibility of Null values in your data?

--
Duane Hookom
MS Access MVP
--

Carol said:
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

Duane Hookom said:
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
--


Carol said:
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
 
C

Carol

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
Duane Hookom said:
And your most recent SQL is what?
Is there a possibility of Null values in your data?

--
Duane Hookom
MS Access MVP
--

Carol said:
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

Duane Hookom said:
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
 
D

Duane Hookom

I don't see where you are using the parameters. They don't show up in the
where clause.

Are all the fields that shold be numeric actually numeric?

--
Duane Hookom
MS Access MVP


Carol said:
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
Duane Hookom said:
And your most recent SQL is what?
Is there a possibility of Null values in your data?

--
Duane Hookom
MS Access MVP
--

Carol said:
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
 
C

Carol

The parameters are at the beginning of the SQL. I was told to put them there
from another posting to microsoft.

Yes, all numeric fields are numeric. The query works when running from the
query itself. An only 1 of the 4 reports is working. That's why I copied
the sql from the working report.
Carol

Duane Hookom said:
I don't see where you are using the parameters. They don't show up in the
where clause.

Are all the fields that shold be numeric actually numeric?

--
Duane Hookom
MS Access MVP


Carol said:
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
Duane Hookom said:
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
 
D

Duane Hookom

I still don't see where you are using any criteria that would require
parameters.

If you can't get this to work as it is, then consider creating a temporary
table and using that as the record source for the report.

--
Duane Hookom
MS Access MVP


Carol said:
The parameters are at the beginning of the SQL. I was told to put them
there
from another posting to microsoft.

Yes, all numeric fields are numeric. The query works when running from
the
query itself. An only 1 of the 4 reports is working. That's why I copied
the sql from the working report.
Carol

Duane Hookom said:
I don't see where you are using the parameters. They don't show up in the
where clause.

Are all the fields that shold be numeric actually numeric?

--
Duane Hookom
MS Access MVP


Carol said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top