Query question

[Ticotion]

Hi

I have a cross tab query that gets week no. input from a form. The cross tab
should show week number, department and a OEE value. I use the following
select statement and I've defined the form input in the parameter menu.

PARAMETERS [Forms]![Frm_fac]![combo5] Text ( 255 ),
[Forms]![Frm_fac]![combo11] Text ( 255 );
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.weeknb
FROM qry_generelOEE
WHERE (((qry_generelOEE.weeknb) Between [Forms]![Frm_fac]![combo5] And
[Forms]![Frm_fac]![combo11]))
GROUP BY qry_generelOEE.weeknb
PIVOT qry_generelOEE.dptnb;

The problem is that I get an error massage saying the statement is to
complex (error statement 3010). Is there anyóne who get help me with a
solution how to simplyfie the query?

Thanks
Ticotion

 

[Allen Browne]

"Too complex" is Access-speak for "can't figure it out." There can be many
reasons for that (e.g. too many ANDs in WHERE), but a common one is that the
data types are not what is expected.

If you open your table, in design view, is weeknb actually a TEXT field? If
so, you will get some very strange results, e.g. weeks 11 through 19 will
sort before week 2 (since text fields sort character-by-character, and the
first character - the 1 - is less than 2.)

If weeknb is a Number field, you need to set up the *bound* column of combo5
and combo11 so that it is a numeric value (regardless of what the combo's
display value actually is.) Once the Value of the combos is the correct
number, set their Format property to General Number (so Access knows they
are numbers), and change the parameters in the query to Integer rather than
Text. It should now be able to apply the numeric numbers as parameters for
the numeric field weeknb.

If that still doesn't work, the problem is probably deeper down (in the
lower level query.)

More info about forcing Access to understand the correct data type:
http://allenbrowne.com/ser-45.html

 

[David W. Fenton]

"Too complex" is Access-speak for "can't figure it out." There can
be many reasons for that (e.g. too many ANDs in WHERE), but a
common one is that the data types are not what is expected.

Another common one is that the existing query compilation is out of
date and for some reason is not being updated. A compact can fix
this, or pasting the SQL into a new query if you don't want to
compact the whole db (which will invalidate all the "good"
compilation of queries).

 

[Ticotion]

Hi

I worked it out. It was simply related to paramter date typ which wass set
as text and not as date

thanks for your help

Ticotion