System Resource Exceeded

  • Thread starter Thread starter sweetpotatop
  • Start date Start date
S

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.
 
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.
 
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.



[email protected] said:
Hi,
Click to expand...
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.
Click to expand...
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.
Click to expand...
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
Click to expand...
Thanks in advance.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
Back
Top