SQL Complex Error

[KaayJaay]

I cant understand why i get this error message"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. (Error 3071)"

When this query is run:

SELECT [Total Repair Repeats].MT_RC AS MT, [Total Repair Repeats].Full_RC AS MU, Count([Total Repair Repeats].[OR_Disp]) AS Repeats, Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) AS Volume, Count([Total Repair Repeats].[OR_Disp])/Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) AS Percentage
FROM [Total Repair Repeats] INNER JOIN [Tech_Data Query] ON ([Total Repair Repeats].[Tech_ID] = [Tech_Data Query].[Tech#]) AND ([Total Repair Repeats].Full_RC = [Tech_Data Query].MU)
WHERE ([Total Repair Repeats].[ClosedDate] Between [Forms]![Repeats Interface]![Start] And [Forms]![Repeats Interface]![End])
AND (([Total Repair Repeats].MT_RC)=[Please enter MT])
AND (([Total Repair Repeats].[OR_Disp])=[Please enter a Disposition code:])
GROUP BY [Total Repair Repeats].MT_RC, [Total Repair Repeats].Full_RC
ORDER BY Count([Total Repair Repeats].[OR_Disp])/Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) DESC;

This one has me bent outta shape here all. Any Suggestions?

 

[SiJP]

From memory I think access only allows a certain number of expressions within a select statement, for one particular field. Peculiar, since your SQL is not that complex.

Anyroad..

Something like your "ORDER BY Count([Total Repair Repeats].[OR_Disp])/Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) DESC;" line may be breaking it.

The best way to debug is to either:

a) chuck it in the QBE and take out one field at a time
-or-
b) take out one expressions from the SQL at a time..

Once you find where the problem is, see if you can get around it or post here for help..

HTH's


------------------------
Hit any User to continue

 

[KaayJaay]

Alright, i will try that thanks.
What is the QBE you are referring too?

KaayJaay

 

[KaayJaay]

What is also interesting, is another query i have that is not sorted with the date criteria above works.

Heres the code:

SELECT [Total Repair Repeats].MT_RC AS MT, [Total Repair Repeats].Full_RC AS MU, Count([Total Repair Repeats].[OR_Disp]) AS Repeats, Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) AS Volume, Count([Total Repair Repeats].[OR_Disp])/Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) AS Percentage
FROM [Total Repair Repeats] INNER JOIN [Tech_Data Query] ON ([Total Repair Repeats].Full_RC = [Tech_Data Query].MU) AND ([Total Repair Repeats].[Tech_ID] = [Tech_Data Query].[Tech#])
WHERE ((([Total Repair Repeats].MT_RC)=[Please enter MT])
AND (([Total Repair Repeats].[OR_Disp])=[Please enter a Disposition code:]))
GROUP BY [Total Repair Repeats].MT_RC, [Total Repair Repeats].Full_RC
ORDER BY Count([Total Repair Repeats].[OR_Disp])/Sum([Tech_Data Query].[VSO]+[Tech_Data Query].[UNB]) DESC;

Thats whats really bugging me, it would be okay if both of them didnt work but the fact that the date criteria is messing up the one is interesting.

KaayJaay

 

[KaayJaay]

Thanks for all of your help, the info about the date i gave you helped me realize my stupidity, I have a form doing the date calculation, but i wasnt using the form when i was running the query, hence, no information was entered in the date text boxes. I ran it in the form, and it worked.

Thanks for your help.
KaayJaay

 

[SiJP]

Hey hey.. good stuff.

My bad.. the QBE is the Query Basic Editor.. basically its the query designer you use in access (Click Queries, select New, voila.. thats the QBE!)

------------------------
Hit any User to continue