Reporting Error 'Can't Find <fieldname> within the expression'

[Constantine AI]

Hi I have created a Purchase Order Report which contains a PO Header Report and a PO Detail Report.

The PO Detail Report contains a Grouping on 'CategoryDescription' followed by the Detail of Stock Items. When i open this query and report up it works fine with no errors.

When i incorporated this sub-report into the PO Header Report it now says that it can't find the 'CategoryDescription'. The Master and Child link between the PO Header and PO Detail is set by the PONo. I can't understand how or why i can't find the 'CategoryDescription', its finds it fine when run on its own but when incorporated into the Header, No.

Does anyone have any suggestions of what i could do? I am using MS Access 2007 as well.

[Stewart Ross]

Hi. This message usually means you have not put a copy of the relevant field onto your report bound to a control. The report will not 'see' the field unless there is a control bound to it somewhere, even though it is a valid field name from the underlying query.

To add that field to your report bound to a textbox control just drag a copy from the field list onto the report at an appropriate location.

The control's visible property can be set to No if you don't need it to be seen within the report.

Let us know how it goes for you.

-Stewart

[Constantine AI]

Hi. This message usually means you have not put a copy of the relevant field onto your report bound to a control. The report will not 'see' the field unless there is a control bound to it somewhere, even though it is a valid field name from the underlying query.

To add that field to your report bound to a textbox control just drag a copy from the field list onto the report at an appropriate location.

The control's visible property can be set to No if you don't need it to be seen within the report.

Let us know how it goes for you.

-Stewart

Sorry for not saying but it is actually visible on the report as well as the query. This is what i can't understand.

[Stewart Ross]

Hi Constantine Al. Would you mind posting the SQL for your subreport query (or at least a relevant part of it, including the joins and any where clause)? It may work in some circumstances but not in others, and without seeing the expression that A2007 is objecting to I cant really advise further at present.

Thanks

Stewart

[Constantine AI]

Hi Constantine Al. Would you mind posting the SQL for your subreport query (or at least a relevant part of it, including the joins and any where clause)? It may work in some circumstances but not in others, and without seeing the expression that A2007 is objecting to I cant really advise further at present.

Thanks

Stewart

Hi Stewart, no problems.

Here is the SQL syntax for the PO Header Report:

SELECT sysmas.SysCusName, sysmas.SysCusAddress1, sysmas.SysCusAddress2, sysmas.SysCusAddress3, sysmas.SysCusAddress4, sysmas.SysCusAddress5, sysmas.SysCusPostCode, sysmas.SysCusPhone, sysmas.SysCusFax, sysmas.SysCusMobile, sysmas.SysCusEmail, sysmas.SysCusVatReg, sysmas.SysPaymentTerms, suppmas.SuppNo, suppmas.Forename, suppmas.Surname, suppmas.Salutation, suppmas.CompanyName, suppmas.Add1, suppmas.Add2, suppmas.Add3, suppmas.Add4, suppmas.Add5, suppmas.Postcode, suppmas.PhoneNo, suppmas.MobileNo, pordhdr.PONo, pordhdr.PODate, pordhdr.DueDate, pordhdr.Notes, suppmas.OurAccountNo, ordhdr.OrderNo, pordhdr.DeliveryName, pordhdr.DeliveryAdd1, pordhdr.DeliveryAdd2, pordhdr.DeliveryAdd3, pordhdr.DeliveryAdd4, pordhdr.DeliveryAdd5, pordhdr.DeliveryPostCode
FROM sysmas, pordhdr INNER JOIN (ordhdr INNER JOIN (suppmas INNER JOIN ordlin ON suppmas.SuppNo = ordlin.SuppNo) ON ordhdr.OrderNo = ordlin.OrderNo) ON (suppmas.SuppNo = pordhdr.SuppNo) AND (pordhdr.PONo = ordlin.PONo)
GROUP BY sysmas.SysCusName, sysmas.SysCusAddress1, sysmas.SysCusAddress2, sysmas.SysCusAddress3, sysmas.SysCusAddress4, sysmas.SysCusAddress5, sysmas.SysCusPostCode, sysmas.SysCusPhone, sysmas.SysCusFax, sysmas.SysCusMobile, sysmas.SysCusEmail, sysmas.SysCusVatReg, sysmas.SysPaymentTerms, suppmas.SuppNo, suppmas.Forename, suppmas.Surname, suppmas.Salutation, suppmas.CompanyName, suppmas.Add1, suppmas.Add2, suppmas.Add3, suppmas.Add4, suppmas.Add5, suppmas.Postcode, suppmas.PhoneNo, suppmas.MobileNo, pordhdr.PONo, pordhdr.PODate, pordhdr.DueDate, pordhdr.Notes, suppmas.OurAccountNo, ordhdr.OrderNo, pordhdr.DeliveryName, pordhdr.DeliveryAdd1, pordhdr.DeliveryAdd2, pordhdr.DeliveryAdd3, pordhdr.DeliveryAdd4, pordhdr.DeliveryAdd5, pordhdr.DeliveryPostCode
HAVING (((pordhdr.PONo)=[Forms]![frmSupplierOrderForm]![txtPONo]));

And this is the SQL syntax for the PO Detail Report:

SELECT catmas.PurchaseOrder, catmas.CategoryDescription, catmas.OrderLevel, Sum(ordlin.Qty) AS SumOfQty, ordlin.WithGrain, ordlin.AcrossGrain, ordlin.StkID, stkmas.StkLongDesc, rngmas.RangeDesc, colmas.ColourDescription, fasmatmas.FasciaMaterialDesc, fasfinmas.FasciaFinishDesc, ordlin.OrderNo, ordlin.PONo, ordlin.Cost, Sum([Qty]*[Cost]) AS LineCost
FROM ((((ordlin INNER JOIN rngmas ON ordlin.RangeID = rngmas.RangeID) INNER JOIN colmas ON (ordlin.ColourID = colmas.ColourID) AND (ordlin.CarcussColourID = colmas.ColourID)) INNER JOIN fasmatmas ON ordlin.FasciaMaterialID = fasmatmas.FasciaMaterialID) INNER JOIN fasfinmas ON ordlin.FasciaFinishID = fasfinmas.FasciaFinishID) INNER JOIN (catmas INNER JOIN stkmas ON catmas.CategoryID = stkmas.CategoryID) ON (rngmas.RangeID = stkmas.RangeID) AND (ordlin.StkID = stkmas.StkID)
GROUP BY catmas.PurchaseOrder, catmas.CategoryDescription, catmas.OrderLevel, ordlin.WithGrain, ordlin.AcrossGrain, ordlin.StkID, stkmas.StkLongDesc, rngmas.RangeDesc, colmas.ColourDescription, fasmatmas.FasciaMaterialDesc, fasfinmas.FasciaFinishDesc, ordlin.OrderNo, ordlin.PONo, ordlin.Cost
HAVING (((catmas.PurchaseOrder)=True))
ORDER BY catmas.OrderLevel, ordlin.OrderNo, ordlin.PONo;

Hope this helps, the master and child link joins are with the PONo, if you need anything else just ask?

p.s. sorry about the klump of sql statements.

[Stewart Ross]

HI Constantine Al. In themselves the two SQL statements seem fine to me - no obvious glitches. As they are each joining multiple tables it suggests to me that the relationship between the two queries is not likely to be as simple as just on purchase order no alone; the error message may well be arising because the database engine is confused in trying to join the header to the detail and finding it cannot do so on the one joining field when there are so many interrelationships involved.

You would need to be sure that the header query returns just ONE row for each purchase order, and that in the multiple rows of the detail query relating to that purchase order each stock item for that order is returned ONCE and no more than that. If either of these queries returns more than one row per item you will get row multiplication occurring - something called the Cartesian product of the two queries - which could easily lead to database engine confusions such as the one you have had.

-Stewart

[Constantine AI]

HI Constantine Al. In themselves the two SQL statements seem fine to me - no obvious glitches. As they are each joining multiple tables it suggests to me that the relationship between the two queries is not likely to be as simple as just on purchase order no alone; the error message may well be arising because the database engine is confused in trying to join the header to the detail and finding it cannot do so on the one joining field when there are so many interrelationships involved.

You would need to be sure that the header query returns just ONE row for each purchase order, and that in the multiple rows of the detail query relating to that purchase order each stock item for that order is returned ONCE and no more than that. If either of these queries returns more than one row per item you will get row multiplication occurring - something called the Cartesian product of the two queries - which could easily lead to database engine confusions such as the one you have had.

-Stewart

Thanks for the advice, i will look into that tonight!