"Query is too complex" Problem

[AccessIM]

Hello All-

I have the following query:

SELECT qryReceivingDateRange.ItemCode,
Min(qryReceivingDateRange.LeadTimeEndDate) AS FirstLeadTimeEndDate
FROM qryReceivingDateRange
GROUP BY qryReceivingDateRange.ItemCode
ORDER BY qryReceivingDateRange.ItemCode,
Min(qryReceivingDateRange.LeadTimeEndDate);

The query will work at some times but not at others.

For example, if I choose a date range of one month, th query runs fine and
returns the minimum date in the LeadTimeEndDate field (LeadTimeEndDate is a
calculated field). However, if I go out to a date range of six weeks, I
receive the error message that my query is too complex.

However...again, if I use the same six week period and enter one item code
as criteria, it will work pulling up only that item code and returning the
minimum date in the LeadTimeEndDate field.

Can anyone shed some light on to this error message for me and maybe why it
works sometimes but not others?

Thank you so much.

 

[John Spencer]

The query that is too complex is probably qryReceivingDateRange and you have
not posted that query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

In addition, why ORDER BY the Min(...) column since there should only be one
record per ItemCode?

 

[AccessIM]

Hi John-

I'm sorry for the delay but I was having a lot of problems replying to your
response and then I was pulled away to work on a different project.

All other queries work fine regardless of the date range I choose.

Here is the SQL of qryReceivingDateRange:

ELECT qryMA302.RcvdDate, qryMA302.[PO#], qryMA302.[Seq#],
qryMA302.[Vendor#], qryMA302.VendorName, qryMA302.Vendor_Lead_Time,
qryMA302.ItemCode, qryMA302.Whse_Zone, qryMA302.OrderQty, qryMA302.RcvdQty,
qryMA302.LeadTimeEndDate
FROM qryMA302
WHERE (((qryMA302.RcvdDate) Between forms!frmMainMenu!BeginningDate And
forms!frmMainMenu!EndingDate));

And here is the SQL for qryMA302 which the query above uses:

SELECT tblMA302.RcvdDate, dbo_vw_ItemFileMRDC.Vendor_Lead_Time,
DateAdd("d",[Vendor_Lead_Time],[RcvdDate]) AS LeadTimeEndDate,
tblMA302.[PO#], tblMA302.[Seq#], tblMA302.[Vendor#], tblMA302.VendorName,
dbo_vw_ItemFileMRDC.Buyer, tblMA302.ItemCode, dbo_vw_ItemFileMRDC.Whse_Zone,
dbo_vw_ItemFileMRDC.Brand, dbo_vw_ItemFileMRDC.Description,
dbo_vw_ItemFileMRDC.Item_Pack, dbo_vw_ItemFileMRDC.Vendor_Pack,
tblMA302.OrderQty, tblMA302.RcvdQty, tblMY508.WhseTie, tblMY508.WhseTier
FROM tblMY508 RIGHT JOIN (tblMA302 LEFT JOIN dbo_vw_ItemFileMRDC ON
tblMA302.ItemCode = dbo_vw_ItemFileMRDC.Item_Code) ON tblMY508.ItemCode =
tblMA302.ItemCode
ORDER BY tblMA302.RcvdDate, tblMA302.[PO#], tblMA302.[Seq#],
tblMA302.ItemCode;