#Name? Error message in report

[saustin]

Hi All,
Have a report based on a query that works great. Wanted to add another text box to the report and base the field on another query. Here is the code.
=(SELECT DISTINCT ([dbo_tblPODetail]![PARTID])
FROM (dbo_tblPODest INNER JOIN dbo_tblOrder ON [dbo_tblPODest]![ORDERID] = [dbo_tblOrder]![ORDERID]) INNER JOIN (dbo_tblPO INNER JOIN dbo_tblPODetail ON dbo_tblPO]![POID = [dbo_tblPODetail]![POID]) ON ([dbo_tblPODest]![POID] = [dbo_tblPODetail]![POID]) AND ([dbo_tblPODest]![LINEID] = [dbo_tblPODetail]![LINEID])
WHERE ((([dbo_tblPODetail]![PARTID])=[QAMFGQry]![PART_ID]) AND (([dbo_tblPO]![SUPPID])=&quot;HSISD&quot AND (([dbo_tblPODetail]![URGENCY])<>&quot;ROUTINE&quot AND (([dbo_tblPODetail]![PODSTATUS])<&quot;90&quot AND (([dbo_tblOrder]![CUSTPO]) Is Not Null)))

When run as a new query this works as expected and returns data. When the code is put into a text box I get the #Name? (eh whats up doc) error. Tried to be real specific and also defined my table relationships. Can anyone spot an error, something I overlooked ?
Thanks for any suggestions ! Steve.

Steve Austin
MIS Department
Helicopter Support, Inc.
(p): (203)795-6051 Ext. 3378
(f): (203)799-5020

 

[JAXDBase]

Helo support eh? You fly? I would love to fly them but too expensive.

Anyway, try this: Save this query in the query box and when you set the properties of the control source for the textbox, select from the dropdown the queryname you just saved that new query to. Or, you can type it directly into the control source textbox. The query should now run and populate your report correctly since Access will run the queries attached to the report B4 creating the report - makes sense to get the data 1st B4 making the report. Access does not understand the entire query inserted verbatim into the record source or if it blank like I suspect it is now. It looks for stored /saved query names or table/column instead.

Hope this helps and keep on flying..

 

[saustin]

Hi Erogla,
Many thanks for the reply ! Should have mentioned that the second query run from the text box is based on the value of the PART field in the detail section of the report.
Did save the query, named it and here is what it looks like.

SELECT DISTINCT ([dbo_tblPODetail].[PARTID]) AS PART
FROM (dbo_tblPODest INNER JOIN dbo_tblOrder ON dbo_tblPODest.ORDERID = dbo_tblOrder.ORDERID) INNER JOIN (dbo_tblPO INNER JOIN dbo_tblPODetail ON dbo_tblPO.POID = dbo_tblPODetail.POID) ON (dbo_tblPODest.POID = dbo_tblPODetail.POID) AND (dbo_tblPODest.LINEID = dbo_tblPODetail.LINEID)
WHERE (((dbo_tblPODetail.PARTID)=[Reports]![QAMFGRP1 COPY]![txtPART_ID]) AND ((dbo_tblPO.SUPPID)=&quot;HSISD&quot AND ((dbo_tblPODetail.URGENCY)<>&quot;ROUTINE&quot AND ((dbo_tblPODetail.PODSTATUS)<&quot;90&quot AND ((dbo_tblOrder.CUSTPO) Is Not Null));

However what is confusing is how to call it. The only option
value I see for the control source property for the text field after selecting the query is [qryHSI HSISD open TNS]![PART]. The result when running this is an Enter Parameter value prompt = qryHSI HSISD open TNS.
Like propellar bladed I seem to be going around in circles. Am missing something obvious.
Thanks, Steve.

 

[UncleJack]

It could be something involved in other data calls being made by your form where you end up with a circular reference by calling values dependent on other queries. It is rather a complex statement - - but, that's generally expected from helicopter types. (I used to fly S-64's).

Uncle Jack

http://www.aviationdata.com

 

[saustin]

Hi Uncle Jack,
Still don't know the reason for the error but have been able to get around the problem by adding a subreport to the detail section based on a query.
Thanks, Steve.