Query not working

G

Guest

I have a query that is working fine on my computer but when I try to run it
on a other computer, I receive the 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).

How can my query be OK for my computer an too complex for a other? I have
created much more complicated query in the past without problem.

Here is my Query, there is nothing complex here to me:

SELECT Sum([QRY-CAROnTimePerformanceByDate].NumberOfCarDue) AS
SumOfNumberOfCarDue, Min([QRY-CAROnTimePerformanceByDate].DueDate) AS
MinOfDueDate, Sum([QRY-CAROnTimePerformanceByDate].CAROnTime) AS
SumOfCAROnTime, Format([DueDate],"mmmm yyyy") AS DueMonth
FROM [QRY-CAROnTimePerformanceByDate]
GROUP BY Format([DueDate],"mmmm yyyy");

Here is QRY-CARonTimePerformanceByDate :

SELECT [QRY-NumberOfCARDueByDate].NumberOfCarDue,
[QRY-NumberOfCARDueByDate].DueDate,
[QRY-NumberOfCAROnTimeByDate].NumberOfCARCloseOnTime AS CAROnTimeRough,
IIf(IsNull([CAROnTimeRough]),0,[CAROnTimeRough]) AS CAROnTime
FROM [QRY-NumberOfCARDueByDate] LEFT JOIN [QRY-NumberOfCAROnTimeByDate] ON
[QRY-NumberOfCARDueByDate].DueDate = [QRY-NumberOfCAROnTimeByDate].DueDate
ORDER BY [QRY-NumberOfCARDueByDate].DueDate;

And the 2 last one Qry-NumberOfCARDueByDate:

SELECT Count(CAR.CAR) AS NumberOfCarDue, CAR.Duedate
FROM CAR
GROUP BY CAR.Duedate
HAVING ((Not (CAR.Duedate) Is Null))
ORDER BY CAR.Duedate;

And QRY-NumberOfCAROnTimeByDate :

SELECT Count(CAR.CAR) AS NumberOfCARCloseOnTime, CAR.Duedate
FROM CAR
WHERE (((CAR.ClosedDate)<=[DueDate]))
GROUP BY CAR.Duedate
ORDER BY CAR.Duedate;

Like I said before, I did much more complex query in the past and this is
the first time it is not working. Any ideas why?
 
G

Guest

Nothing really jumps out. Sometimes Access gets a little picky. Some things
to try:

IsNull([CAROnTimeRough]) = True

HAVING IsNull(CAR.Duedate) = False

Since the problem is on a different computer, check that computer for
service packs and service patches. Also look for a reference problem. Maybe
try the database on a third computer and see what happens there.
 
G

Guest

Thanks Jerry for your reply.

I tried your suggestion and it makes me realised that access is bugging
about nothing else but date format : Format([DueDate],"mmmm yyyy") AS
DueMonth ... GROUP BY Format([DueDate],"mmmm yyyy");

If I remove it from the query, the problem is gone. But then the query does
not return what I want.

Do you know a other way to have my query group by month?

A other thing, I tried it on 2 more computer with the same result. I checked
for the service pack and we are all the same. My computer must have something
special. Like you said it might be a reference problem. I will check it out.

Thanks,

Yanick
Jerry Whittle said:
Nothing really jumps out. Sometimes Access gets a little picky. Some things
to try:

IsNull([CAROnTimeRough]) = True

HAVING IsNull(CAR.Duedate) = False

Since the problem is on a different computer, check that computer for
service packs and service patches. Also look for a reference problem. Maybe
try the database on a third computer and see what happens there.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Yanick said:
I have a query that is working fine on my computer but when I try to run it
on a other computer, I receive the 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).

How can my query be OK for my computer an too complex for a other? I have
created much more complicated query in the past without problem.

Here is my Query, there is nothing complex here to me:

SELECT Sum([QRY-CAROnTimePerformanceByDate].NumberOfCarDue) AS
SumOfNumberOfCarDue, Min([QRY-CAROnTimePerformanceByDate].DueDate) AS
MinOfDueDate, Sum([QRY-CAROnTimePerformanceByDate].CAROnTime) AS
SumOfCAROnTime, Format([DueDate],"mmmm yyyy") AS DueMonth
FROM [QRY-CAROnTimePerformanceByDate]
GROUP BY Format([DueDate],"mmmm yyyy");

Here is QRY-CARonTimePerformanceByDate :

SELECT [QRY-NumberOfCARDueByDate].NumberOfCarDue,
[QRY-NumberOfCARDueByDate].DueDate,
[QRY-NumberOfCAROnTimeByDate].NumberOfCARCloseOnTime AS CAROnTimeRough,
IIf(IsNull([CAROnTimeRough]),0,[CAROnTimeRough]) AS CAROnTime
FROM [QRY-NumberOfCARDueByDate] LEFT JOIN [QRY-NumberOfCAROnTimeByDate] ON
[QRY-NumberOfCARDueByDate].DueDate = [QRY-NumberOfCAROnTimeByDate].DueDate
ORDER BY [QRY-NumberOfCARDueByDate].DueDate;

And the 2 last one Qry-NumberOfCARDueByDate:

SELECT Count(CAR.CAR) AS NumberOfCarDue, CAR.Duedate
FROM CAR
GROUP BY CAR.Duedate
HAVING ((Not (CAR.Duedate) Is Null))
ORDER BY CAR.Duedate;

And QRY-NumberOfCAROnTimeByDate :

SELECT Count(CAR.CAR) AS NumberOfCARCloseOnTime, CAR.Duedate
FROM CAR
WHERE (((CAR.ClosedDate)<=[DueDate]))
GROUP BY CAR.Duedate
ORDER BY CAR.Duedate;

Like I said before, I did much more complex query in the past and this is
the first time it is not working. Any ideas why?
Click to expand...
Click to expand...
 
G

Guest

I got progress

If I change the Format([DueDate],"mmmm yyyy") AS
DueMonth ... GROUP BY Format([DueDate],"mmmm yyyy");
By
Year([DueDate]) & Month([DueDate]) AS DueDateYearMonth ...
GROUP BY Year([DueDate]) & Month([DueDate]);
The query result is what I want.

But then the my chart (report) which is grouping by month still give me the
same error. At least there is progress.

Jerry Whittle said:
Nothing really jumps out. Sometimes Access gets a little picky. Some things
to try:

IsNull([CAROnTimeRough]) = True

HAVING IsNull(CAR.Duedate) = False

Since the problem is on a different computer, check that computer for
service packs and service patches. Also look for a reference problem. Maybe
try the database on a third computer and see what happens there.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Yanick said:
I have a query that is working fine on my computer but when I try to run it
on a other computer, I receive the 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).

How can my query be OK for my computer an too complex for a other? I have
created much more complicated query in the past without problem.

Here is my Query, there is nothing complex here to me:

SELECT Sum([QRY-CAROnTimePerformanceByDate].NumberOfCarDue) AS
SumOfNumberOfCarDue, Min([QRY-CAROnTimePerformanceByDate].DueDate) AS
MinOfDueDate, Sum([QRY-CAROnTimePerformanceByDate].CAROnTime) AS
SumOfCAROnTime, Format([DueDate],"mmmm yyyy") AS DueMonth
FROM [QRY-CAROnTimePerformanceByDate]
GROUP BY Format([DueDate],"mmmm yyyy");

Here is QRY-CARonTimePerformanceByDate :

SELECT [QRY-NumberOfCARDueByDate].NumberOfCarDue,
[QRY-NumberOfCARDueByDate].DueDate,
[QRY-NumberOfCAROnTimeByDate].NumberOfCARCloseOnTime AS CAROnTimeRough,
IIf(IsNull([CAROnTimeRough]),0,[CAROnTimeRough]) AS CAROnTime
FROM [QRY-NumberOfCARDueByDate] LEFT JOIN [QRY-NumberOfCAROnTimeByDate] ON
[QRY-NumberOfCARDueByDate].DueDate = [QRY-NumberOfCAROnTimeByDate].DueDate
ORDER BY [QRY-NumberOfCARDueByDate].DueDate;

And the 2 last one Qry-NumberOfCARDueByDate:

SELECT Count(CAR.CAR) AS NumberOfCarDue, CAR.Duedate
FROM CAR
GROUP BY CAR.Duedate
HAVING ((Not (CAR.Duedate) Is Null))
ORDER BY CAR.Duedate;

And QRY-NumberOfCAROnTimeByDate :

SELECT Count(CAR.CAR) AS NumberOfCARCloseOnTime, CAR.Duedate
FROM CAR
WHERE (((CAR.ClosedDate)<=[DueDate]))
GROUP BY CAR.Duedate
ORDER BY CAR.Duedate;

Like I said before, I did much more complex query in the past and this is
the first time it is not working. Any ideas why?
Click to expand...
Click to expand...
 
G

Guest

I finally manage to get it work. For the only purpose of the news group (if
someone else have the same problem or similar) here is the SQL of my chart :

SELECT month([MinOfDueDate]) & "/" &
year([MinOfDueDate]),sum([SumOfNumberOfCarDue]) AS [Number Of Car
Due],avg([DuePercent]) AS [Percent],sum([SumOfCAROnTime]) AS [CAR On Time]
FROM [QRY-CAROnTimePerformanceByMonth-Percent-2004-2005] GROUP BY
(Year([MinOfDueDate])*12 + Month([MinOfDueDate])-1),month([MinOfDueDate]) &
"/" & year([MinOfDueDate]);

Maybe it does not look like but you help me a lot Jerry, thanks again.

Jerry Whittle said:
Nothing really jumps out. Sometimes Access gets a little picky. Some things
to try:

IsNull([CAROnTimeRough]) = True

HAVING IsNull(CAR.Duedate) = False

Since the problem is on a different computer, check that computer for
service packs and service patches. Also look for a reference problem. Maybe
try the database on a third computer and see what happens there.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Yanick said:
I have a query that is working fine on my computer but when I try to run it
on a other computer, I receive the 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).

How can my query be OK for my computer an too complex for a other? I have
created much more complicated query in the past without problem.

Here is my Query, there is nothing complex here to me:

SELECT Sum([QRY-CAROnTimePerformanceByDate].NumberOfCarDue) AS
SumOfNumberOfCarDue, Min([QRY-CAROnTimePerformanceByDate].DueDate) AS
MinOfDueDate, Sum([QRY-CAROnTimePerformanceByDate].CAROnTime) AS
SumOfCAROnTime, Format([DueDate],"mmmm yyyy") AS DueMonth
FROM [QRY-CAROnTimePerformanceByDate]
GROUP BY Format([DueDate],"mmmm yyyy");

Here is QRY-CARonTimePerformanceByDate :

SELECT [QRY-NumberOfCARDueByDate].NumberOfCarDue,
[QRY-NumberOfCARDueByDate].DueDate,
[QRY-NumberOfCAROnTimeByDate].NumberOfCARCloseOnTime AS CAROnTimeRough,
IIf(IsNull([CAROnTimeRough]),0,[CAROnTimeRough]) AS CAROnTime
FROM [QRY-NumberOfCARDueByDate] LEFT JOIN [QRY-NumberOfCAROnTimeByDate] ON
[QRY-NumberOfCARDueByDate].DueDate = [QRY-NumberOfCAROnTimeByDate].DueDate
ORDER BY [QRY-NumberOfCARDueByDate].DueDate;

And the 2 last one Qry-NumberOfCARDueByDate:

SELECT Count(CAR.CAR) AS NumberOfCarDue, CAR.Duedate
FROM CAR
GROUP BY CAR.Duedate
HAVING ((Not (CAR.Duedate) Is Null))
ORDER BY CAR.Duedate;

And QRY-NumberOfCAROnTimeByDate :

SELECT Count(CAR.CAR) AS NumberOfCARCloseOnTime, CAR.Duedate
FROM CAR
WHERE (((CAR.ClosedDate)<=[DueDate]))
GROUP BY CAR.Duedate
ORDER BY CAR.Duedate;

Like I said before, I did much more complex query in the past and this is
the first time it is not working. Any ideas why?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Query Woes 1
first record query 7
When Filtering Query - Query returns no records when there arer matching records 2
Problem editing a query based on another query and table 3
query not working right 3
UNION SELECT "(ALL)" Query Not Working 6
Using a Form to Create a Query. Problem with VBA code... 2
Invalid use of null 3

Top