Report parameter "too complex"

  • Thread starter Thread starter OldGuy
  • Start date Start date
O

OldGuy

Database runs from 2002 to present. I have a query that works properly and
provides dataset for any year entered. However, report based on query will
only return data for 2002 and 2008. Any other year I get an error
"Expression typed incorrectly, or is too complex to be evaluated..." Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of time I
have spent on it. Thanks in advance :-)
 
Database runs from 2002 to present. I have a query that works properly and
provides dataset for any year entered. However, report based on query will
only return data for 2002 and 2008. Any other year I get an error
"Expression typed incorrectly, or is too complex to be evaluated..." Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of time I
have spent on it. Thanks in advance :-)
Click to expand...

You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as well
as anything you have in the report's Filter property (on the property
sheet's Data tab).
Also, if you are opening the report using code, post the code as well.
 
Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance, qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance, qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and closed.
When you open the report you get the Enter Year parameter box. If you enter
2002 or 2008 report has correct data. If you enter any other year 2003 thru
2007 you get the error message above.
 
It's sometimes possible to fix this problem by formally declaring the
parameter(s) you are using:

PARAMETERS [What Year?] Long;
SELECT ...

If the FY field is a text string, rather than a number (eg. it's been
calculated using a FORMAT expression on a date/time field), then you'll need
to declare the parameter as Text.

HTH,

Rob

OldGuy said:
Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance, qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance, qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and
closed.
When you open the report you get the Enter Year parameter box. If you
enter
2002 or 2008 report has correct data. If you enter any other year 2003
thru
2007 you get the error message above.


fredg said:
You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as well
as anything you have in the report's Filter property (on the property
sheet's Data tab).
Also, if you are opening the report using code, post the code as well.
Click to expand...
Click to expand...
 
Thanks, didn’t work. Without the declaration query worked for each year, but
report worked only for 2002 and 2008. With declaration query and report do
not work for any year.

Rob Parker said:
It's sometimes possible to fix this problem by formally declaring the
parameter(s) you are using:

PARAMETERS [What Year?] Long;
SELECT ...

If the FY field is a text string, rather than a number (eg. it's been
calculated using a FORMAT expression on a date/time field), then you'll need
to declare the parameter as Text.

HTH,

Rob

OldGuy said:
Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance, qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance, qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and
closed.
When you open the report you get the Enter Year parameter box. If you
enter
2002 or 2008 report has correct data. If you enter any other year 2003
thru
2007 you get the error message above.


fredg said:
On Wed, 2 Apr 2008 11:40:01 -0700, OldGuy wrote:

Database runs from 2002 to present. I have a query that works properly
and
provides dataset for any year entered. However, report based on query
will
only return data for 2002 and 2008. Any other year I get an error
"Expression typed incorrectly, or is too complex to be evaluated..."
Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of time
I
have spent on it. Thanks in advance :-)

You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as well
as anything you have in the report's Filter property (on the property
sheet's Data tab).
Also, if you are opening the report using code, post the code as well.
Click to expand...
Click to expand...
Click to expand...
 
What is the data type of the field qryRWAall.FY?

What is the SQL of qryRWAall?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OldGuy said:
Thanks, didn't work. Without the declaration query worked for each year,
but
report worked only for 2002 and 2008. With declaration query and report
do
not work for any year.

Rob Parker said:
It's sometimes possible to fix this problem by formally declaring the
parameter(s) you are using:

PARAMETERS [What Year?] Long;
SELECT ...

If the FY field is a text string, rather than a number (eg. it's been
calculated using a FORMAT expression on a date/time field), then you'll
need
to declare the parameter as Text.

HTH,

Rob

OldGuy said:
Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is
Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and
closed.
When you open the report you get the Enter Year parameter box. If you
enter
2002 or 2008 report has correct data. If you enter any other year 2003
thru
2007 you get the error message above.


:

On Wed, 2 Apr 2008 11:40:01 -0700, OldGuy wrote:

Database runs from 2002 to present. I have a query that works
properly
and
provides dataset for any year entered. However, report based on
query
will
only return data for 2002 and 2008. Any other year I get an error
"Expression typed incorrectly, or is too complex to be evaluated..."
Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of
time
I
have spent on it. Thanks in advance :-)

You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as well
as anything you have in the report's Filter property (on the property
sheet's Data tab).
Also, if you are opening the report using code, post the code as well.
Click to expand...
Click to expand...
Click to expand...
 
I'm not surre. It's the result of the following calculation:

FY: IIf(IsNull([Issued Date]),"UNK",Format(DateAdd("m",3,[Issued
Date]),"yyyy"))

and the query below returns the correct data for all years, 2002 thru 2008.
The report will not.

John Spencer said:
What is the data type of the field qryRWAall.FY?

What is the SQL of qryRWAall?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OldGuy said:
Thanks, didn't work. Without the declaration query worked for each year,
but
report worked only for 2002 and 2008. With declaration query and report
do
not work for any year.

Rob Parker said:
It's sometimes possible to fix this problem by formally declaring the
parameter(s) you are using:

PARAMETERS [What Year?] Long;
SELECT ...

If the FY field is a text string, rather than a number (eg. it's been
calculated using a FORMAT expression on a date/time field), then you'll
need
to declare the parameter as Text.

HTH,

Rob

Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is
Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and
closed.
When you open the report you get the Enter Year parameter box. If you
enter
2002 or 2008 report has correct data. If you enter any other year 2003
thru
2007 you get the error message above.


:

On Wed, 2 Apr 2008 11:40:01 -0700, OldGuy wrote:

Database runs from 2002 to present. I have a query that works
properly
and
provides dataset for any year entered. However, report based on
query
will
only return data for 2002 and 2008. Any other year I get an error
"Expression typed incorrectly, or is too complex to be evaluated..."
Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of
time
I
have spent on it. Thanks in advance :-)

You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as well
as anything you have in the report's Filter property (on the property
sheet's Data tab).
Also, if you are opening the report using code, post the code as well.
Click to expand...
Click to expand...
Click to expand...
 
That expression will returns a string. So the Field is a string (or an
error value)

I would expect this query to work.

Parameters [What Year?] Text (10);
SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance
, qryRWAall.[GSA Official]
, Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall

WHERE qryRWAall.FY=[What Year?]
AND qryRWAall.Cancelled Is Null
AND qryRWAall.Closed Is Null

GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance
, qryRWAall.[GSA Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];

If it fails, then you need to go back to look at qryRWAall and see if it is
generating any errors. Scroll all the way down to the bottom of the query
results to check to see if it generates any errors.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OldGuy said:
I'm not surre. It's the result of the following calculation:

FY: IIf(IsNull([Issued Date]),"UNK",Format(DateAdd("m",3,[Issued
Date]),"yyyy"))

and the query below returns the correct data for all years, 2002 thru
2008.
The report will not.

John Spencer said:
What is the data type of the field qryRWAall.FY?

What is the SQL of qryRWAall?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OldGuy said:
Thanks, didn't work. Without the declaration query worked for each
year,
but
report worked only for 2002 and 2008. With declaration query and
report
do
not work for any year.

:

It's sometimes possible to fix this problem by formally declaring the
parameter(s) you are using:

PARAMETERS [What Year?] Long;
SELECT ...

If the FY field is a text string, rather than a number (eg. it's been
calculated using a FORMAT expression on a date/time field), then
you'll
need
to declare the parameter as Text.

HTH,

Rob

Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is
Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and
closed.
When you open the report you get the Enter Year parameter box. If
you
enter
2002 or 2008 report has correct data. If you enter any other year
2003
thru
2007 you get the error message above.


:

On Wed, 2 Apr 2008 11:40:01 -0700, OldGuy wrote:

Database runs from 2002 to present. I have a query that works
properly
and
provides dataset for any year entered. However, report based on
query
will
only return data for 2002 and 2008. Any other year I get an
error
"Expression typed incorrectly, or is too complex to be
evaluated..."
Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of
time
I
have spent on it. Thanks in advance :-)

You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as
well
as anything you have in the report's Filter property (on the
property
sheet's Data tab).
Also, if you are opening the report using code, post the code as
well.
Click to expand...
Click to expand...
Click to expand...
 
Thanks John,
Your query works the same as mine except I have to enter the year twice,
probably the Parameter statement. And, like mine, it gives me accurate info
for each year. However, like mine, the report based on it gives me the error
message ("Expression typed incorrectly, or is too complex to be
evaluated...") for all years except 2002 and 2008. I checked the qryRWAall
for data errors, I could find none.
It shouldn’t be this difficult. I’ll have to find a way around it. I
appreciate your help


John Spencer said:
That expression will returns a string. So the Field is a string (or an
error value)

I would expect this query to work.

Parameters [What Year?] Text (10);
SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance
, qryRWAall.[GSA Official]
, Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall

WHERE qryRWAall.FY=[What Year?]
AND qryRWAall.Cancelled Is Null
AND qryRWAall.Closed Is Null

GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance
, qryRWAall.[GSA Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];

If it fails, then you need to go back to look at qryRWAall and see if it is
generating any errors. Scroll all the way down to the bottom of the query
results to check to see if it generates any errors.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

OldGuy said:
I'm not surre. It's the result of the following calculation:

FY: IIf(IsNull([Issued Date]),"UNK",Format(DateAdd("m",3,[Issued
Date]),"yyyy"))

and the query below returns the correct data for all years, 2002 thru
2008.
The report will not.

John Spencer said:
What is the data type of the field qryRWAall.FY?

What is the SQL of qryRWAall?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks, didn't work. Without the declaration query worked for each
year,
but
report worked only for 2002 and 2008. With declaration query and
report
do
not work for any year.

:

It's sometimes possible to fix this problem by formally declaring the
parameter(s) you are using:

PARAMETERS [What Year?] Long;
SELECT ...

If the FY field is a text string, rather than a number (eg. it's been
calculated using a FORMAT expression on a date/time field), then
you'll
need
to declare the parameter as Text.

HTH,

Rob

Thanks.

Here's the query SQL

SELECT qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], Count(qryRWAall.[RWA Number]) AS [CountOfRWA Number]
FROM qryRWAall
GROUP BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number],
qryRWAall.Agency, qryRWAall.RWAtotal, qryRWAall.RWABalance,
qryRWAall.[GSA
Official], qryRWAall.Cancelled, qryRWAall.Closed
HAVING (((qryRWAall.FY)=[What Year?]) AND ((qryRWAall.Cancelled) Is
Null)
AND ((qryRWAall.Closed) Is Null))
ORDER BY qryRWAall.FY, qryRWAall.ST, qryRWAall.City, qryRWAall.[RWA
Number];
Query returns the correct data set.

Report based on query contains the same fields except cancelled and
closed.
When you open the report you get the Enter Year parameter box. If
you
enter
2002 or 2008 report has correct data. If you enter any other year
2003
thru
2007 you get the error message above.


:

On Wed, 2 Apr 2008 11:40:01 -0700, OldGuy wrote:

Database runs from 2002 to present. I have a query that works
properly
and
provides dataset for any year entered. However, report based on
query
will
only return data for 2002 and 2008. Any other year I get an
error
"Expression typed incorrectly, or is too complex to be
evaluated..."
Only 4
digits are being entered as in the query.

It doesn't appear that it should be as difficult as the amount of
time
I
have spent on it. Thanks in advance :-)

You can see your database, we can't.
Please post the query SQL (copy and paste it into a message) as
well
as anything you have in the report's Filter property (on the
property
sheet's Data tab).
Also, if you are opening the report using code, post the code as
well.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top