By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 971 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

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

100+
P: 129
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.
Sep 9 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
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
Sep 9 '08 #2

100+
P: 129
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.
Sep 9 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Sep 9 '08 #4

100+
P: 129
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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)
  3. 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
  4. HAVING (((pordhdr.PONo)=[Forms]![frmSupplierOrderForm]![txtPONo]));
And this is the SQL syntax for the PO Detail Report:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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)
  3. 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
  4. HAVING (((catmas.PurchaseOrder)=True))
  5. 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.
Sep 9 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Sep 9 '08 #6

100+
P: 129
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!
Sep 9 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.