Expression is typed incorrectly, or it is too complex to be evaluated

[INeedAccessHelp]

Hello all! I'm having a problem with a crosstab query I've created. The query in SQL view is as follows:

TRANSFORM Sum([Casesm1]+[Casesm2]+[Casesm3]+[Casesm4]+[Casesm5]+[Casesm6]+[Casesm7]
+[Casesm8]+[Casesm9]+[Casesm10]+[Casesm11])/Sum([Pvol1]+[Pvol2]+[Pvol3]+
[Pvol4]+[Pvol5]+[Pvol6]+[Pvol7]+[Pvol8]+[Pvol9]+[Pvol10]+[Pvol11]) AS Efficiency
SELECT tblVolume.Dayofweek, tblVolume.EntryDate, tblVolume.Shift, Sum([Efficiency]) AS [Efficiency Total]
FROM tblVolume
WHERE (((tblVolume.Line)="001" Or (tblVolume.Line)="002" Or (tblVolume.Line)="003" Or (tblVolume.Line)="004" Or (tblVolume.Line)="005" Or (tblVolume.Line)="006" Or (tblVolume.Line)="007" Or (tblVolume.Line)="008" Or (tblVolume.Line)="009" Or (tblVolume.Line)="010" Or (tblVolume.Line)="011" Or (tblVolume.Line)="012" Or (tblVolume.Line)="015") AND ((tblVolume.EntryDate) Between [Forms]![frmReports]![FromDate] And [Forms]![frmReports]![ToDate]))
GROUP BY tblVolume.Dayofweek, tblVolume.EntryDate, tblVolume.Shift
ORDER BY tblVolume.EntryDate, tblVolume.Shift
PIVOT tblVolume.Line In ("001","002","003","004","005","006","007","008","009","010","011",
"012","015");


The error I'm getting is:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.


FYI - When I remove the "/Sum([Pvol1]+[Pvol2]+[Pvol3]+[Pvol4]+[Pvol5]+[Pvol6]+[Pvol7]+[Pvol8]+
[Pvol9]+[Pvol10]+[Pvol11])" part of Efficiency, the query returns the correct results.

Thanks in advance for your help/answers/comments.

--Rob

 

[rac2]

My guess is the expression is too complex, too long, or is typed incorrectly.

Seriously though. The expression suggests to me that there are some database table design problems too. When I see column names like [Casesm11], [Casesm10], or [Pvol3]+[Pvol4], I start thinking that a different table should be defined.

For example, if you have a statistic about the number of cases on a line for each month of the year and a statistic on the Pvol also for a given line for each month then you might define a table named LineStats with columns LineID, Month, Cases, and Pvol. That would allow you to write a query like

SELECT LineStats.LineID, LineStats.Month
SUM([Cases])/SUM([Pvol]) AS Efficiency
FROM LineStats
WHERE LineStats.LineID = "001"
   Or LineStats.LineID.LineID = "002" 
   etc   

ORDER BY LineStats.Month

which has a simple expression for summing Cases and Pvol for each month.

Is it possible that you have adapted your data tracking and reporting from a system originally built as a spreadsheet? The many places where there are repeatition of similarly named and valued pieces suggests that to me.

I will say that the query you posted is extraordinarily complex to me. And I confess I never use the crosstab feature. But I do feel that your problem may not be solved until you restructure your data using a relational database model.

Hope this helps. Ignore it if not.

 

[dhookom]

In addition to the spreadsheetlike structure, you don't need:
[tt][blue]
WHERE (tblVolume.Line)="001" Or (tblVolume.Line)="002"...
[/blue][/tt]
You are already filtering your data by providing Column headings:
[tt][blue]
PIVOT tblVolume.Line In ("001","002",...
[/blue][/tt]
Also, your expression isn't guarding against null values or a possible denominator of 0.

Most crosstabs would require you to enter the data types of
[Forms]![frmReports]![FromDate] And [Forms]![frmReports]![ToDate]. Using the Column Headings property kinda fixes this but I would still select Query->Parameters:
[tt][blue]
[Forms]![frmReports]![FromDate] DateTime
[Forms]![frmReports]![ToDate] DateTime
[/blue][/tt]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

the error is common / rampant in older versions of Ms. A. there it simply means that the query parser ran out of space in the chacking of the query syntax. The query its slef may (or may NOT) be syntatically or functionally correct. If the results stand up to the desk check review, it is almost certaainly the issue with the parser. If, on the otherhand, (per your implication) the query either does not return results or the results fail the desk check you do need to fix it. One painful but reasonably straaighforward approach to the fixing is to divide and conquor. I usually start this by divining the key field of the results set and generate a process (hopefully JUST a query) to return that field populated correctly as a make table or update query (with the necessary fields in the make table). Next step (actually a prior step) is to either delete the table (for make table queries) or a delete all records query for the update query approach. THEN, proceed to populate the table's other / missing fields as ye may, the " ... as ye may ... " part is to generate update queries and / or procedures to populat the empty fileds. Finally, base your report on the table or a simple select query based on the table.

Sorry to be so wordy, can't think of an easy way to state it ...




MichaelRed