If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query question
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 |
#2
|
|||
|
|||
Query question
"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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ticotion" wrote in message ... 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 |
#3
|
|||
|
|||
Query question
"Allen Browne" wrote in
: "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). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#4
|
|||
|
|||
Query question
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 "Ticotion" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|