Whats wrong with this Query

ChampionDuy

Registered User.
Local time
Today, 14:59
Joined
Mar 14, 2002
Messages
94
I have a query Identical to this one that works fine but this one just gets the same data from a different table. Can you see whats wrong with it. I get a Message saying that it may be too complicated or complex and I may want to assign variables.

Bad one
PARAMETERS [Enter last two digits of year:] Value;
SELECT Mid$([CCN],9,4)+" " AS Region, Sum([CASE1].[Request]) AS Requests, AVG(Year) as Yr
FROM CASE1
group BY Mid$([CCN],9,4), Mid$([CCN],6,2)
HAVING (((Sum(CASE1.Request))>=1) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASE1.Request))>=1))
UNION select "TOTAL " AS Region, Sum([CASE1].[Request]) AS Requests, AVG(Year) as Yr from case1
HAVING (((Sum(CASE1.Request))>=1) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASE1.Request))>=1));


This is the one that works fine

Good one
PARAMETERS [Enter last two digits of year:] Value;
SELECT Mid$([CCN],9,4)+" " AS Region, Sum([CASEFILE].[Request]) AS Requests, AVG(Year) as Yr
FROM CASEFILE
group BY Mid$([CCN],9,4), Mid$([CCN],6,2)
HAVING (((Sum(CASEFILE.Request))>=0) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASEFILE.Request))>=0))
UNION select "TOTAL " AS Region, Sum([CASEFILE].[Request]) AS Requests, AVG(Year) as Yr from casefile
HAVING (((Sum(CASEFILE.Request))>=0) AND ((Mid$([CCN],6,2))=[Enter last two digits of year:]) AND ((Count(CASEFILE.Request))>=0));
 
I tested both queries, they just work fine . The error(s) could be caused by differences in the datatype for similar columns in your tables CASE1 and CASEFILE and/ or referring to an non-existing column in your table CASE1 (that's what could be indicated by your error message talking 'bout variables).

RV
 
You are grouping by this string but not showing it in the Select clause - Mid$([CCN],6,2). Try showing it also so that you can determine that it is not causing the problem. Or - eliminate it.
 
I figured it out

I am an Idoit!!!. There was a null value in my table. That was causing the problem. Thanks for the help.
 

Users who are viewing this thread

Back
Top Bottom