System Resource Exceeded

[sweetpotatop]

Hi,

I received "System Resource Exceeded" error when I tried to run the
following query in MS Access. This query isn't complicated at all. And
the mdb is only 12MB.

I've tried to removed one of the result column that required
calculation (e.g. TotalTapes), then it works. But I need them all.
What can I do? Please help.

SELECT Contract.RecordID,
[#ofEps].TotalEps,
IIf(IsNull([TotalSet]),0,[TotalSet])*[TotalEps] AS TotalTapes,
[Total Delivered Per Record].TotalDelivered,
[TotalTapes]-[TotalDelivered] AS Out,
IIf([Out]<0,"0",[Out]) AS Outstanding,
IIf([Out]<0 And [TotalTapes]=[TotalEps],[TotalEps],[TotalDelivered])
AS TotalDel
FROM (Contract INNER JOIN [#ofEps] ON Contract.RecordID =
[#ofEps].RecordID)
INNER JOIN [Total Delivered Per Record] ON Contract.RecordID = [Total
Delivered Per Record].RecordID

Thanks in advance.

 

[Jerry Whittle]

You are creating both TotalTapes and Out fields from IIf statements then
using them within other IIf statements. that might just be the straw that
broke the came's back.

I would try creating another query to gather the fields that you need
including TotalTapes and Out. Then use that query as the record source to do
the final returns including TotalDel and Outstanding.

 

[sweetpotatop]

You are creating both TotalTapes and Out fields from IIf statements then
using them within other IIf statements. that might just be the straw that
broke the came's back.

I would try creating another query to gather the fields that you need
including TotalTapes and Out. Then use that query as the record source todo
the final returns including TotalDel and Outstanding.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,

I received "System Resource Exceeded" error when I tried to run the
following query in MS Access. This query isn't complicated at all. And
the mdb is only 12MB.

I've tried to removed one of the result column that required
calculation (e.g. TotalTapes), then it works. But I need them all.
What can I do? Please help.

SELECT Contract.RecordID,
[#ofEps].TotalEps,
IIf(IsNull([TotalSet]),0,[TotalSet])*[TotalEps] AS TotalTapes,
[Total Delivered Per Record].TotalDelivered,
[TotalTapes]-[TotalDelivered] AS Out,
IIf([Out]<0,"0",[Out]) AS Outstanding,
IIf([Out]<0 And [TotalTapes]=[TotalEps],[TotalEps],[TotalDelivered])
AS TotalDel
FROM (Contract INNER JOIN [#ofEps] ON Contract.RecordID =
[#ofEps].RecordID)
INNER JOIN [Total Delivered Per Record] ON Contract.RecordID = [Total
Delivered Per Record].RecordID

Thanks in advance.- Hide quoted text -

- Show quoted text -

Thanks for you advice. I tried as you suggested. But I am still
getting the same error. But it works fine if I just remove one column.
E.g "Out" or "TotalTapes". Please advice.

SELECT Contract.RecordID,
t.TotalEps,
t.TotalTapes,
t.TotalDelivered, Out,
IIf([Out]<0,"0",[Out]) AS Outstanding,
IIf([Out]<0 And [TotalTapes]=[TotalEps],[TotalEps],[TotalDelivered])
AS TotalDel

FROM Contract INNER JOIN

(select Contract.RecordID,
[#ofEps].TotalEps,
[Total Delivered Per Record].TotalDelivered,
IIf(IsNull([TotalSet]),0,[TotalSet])*[TotalEps] AS TotalTapes,
[TotalTapes]-[TotalDelivered] AS Out
FROM (Contract
INNER JOIN
[#ofEps]
ON Contract.RecordID=[#ofEps].RecordID )
INNER JOIN
[Total Delivered Per Record]
ON Contract.RecordID=[Total Delivered Per Record].RecordID) t

ON Contract.RecordID=t.RecordID