Reference field on Form in Query 1

[jw45]

I am trying to use the code below in my query to reference a field on a form. I'm using Access 2002 SP2.

 <=[Forms]![frmShipRequire]![EndDate]

When I execute the query I get the error message:

"The Microsoft jet database engine does not recognize '[Forms]![frmShipRequire]![EndDate]' as a valid field name or expression"

I have done this many times in Access 97 using the syntax above. I have verifed that the "Name" in the properties is 'EndDate'.

Any clues?

Thanks

 

[PHV]

Is frmShipRequire an open main form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw45]

Yes.. the form is open.... I actually call the query using the DoCmD.OpenQuery from within the form SQL.

 

[PHV]

Can you please post the SQL code of this query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw45]

Here you go PHV.

Changing from "<=[Forms]![frmShipRequire]![EndDate]" to "<=#6/6/05#" and the query works fine.

SELECT tblOrderBook.PL, tblOrderBook.Orig_Prom, tblOrderBook.Cur_Prom, tblOrderBook.Item_Number, tblOrderBook.OrderNum, tblBOM.Desc, tblOrderBook.KanBan, qryShipCrosstab_1.TotalShipQty, tblOrderBook.OrdQty, nz([OrdQty],0)-nz([TotalShipQty],0) AS RemQty, tblOrderBook.Status, tblCustomerInfo.Customer_Name, tblOrderBook.Ship_Comp, tblOHInv.Quantity AS OHQty
FROM tblCustomerInfo INNER JOIN (((tblOrderBook INNER JOIN tblBOM ON tblOrderBook.Item_Number = tblBOM.Item) LEFT JOIN tblOHInv ON tblOrderBook.Item_Number = tblOHInv.Item) LEFT JOIN qryShipCrosstab AS qryShipCrosstab_1 ON tblOrderBook.OrderNum = qryShipCrosstab_1.OrderNum) ON tblCustomerInfo.Plant_ID = tblOrderBook.PL
GROUP BY tblOrderBook.PL, tblOrderBook.Orig_Prom, tblOrderBook.Cur_Prom, tblOrderBook.Item_Number, tblOrderBook.OrderNum, tblBOM.Desc, tblOrderBook.KanBan, qryShipCrosstab_1.TotalShipQty, tblOrderBook.OrdQty, nz([OrdQty],0)-nz([TotalShipQty],0), tblOrderBook.Status, tblCustomerInfo.Customer_Name, tblOrderBook.Ship_Comp, tblOHInv.Quantity
HAVING (((tblOrderBook.Orig_Prom)<=[Forms]![frmShipRequire]![EndDate]) AND ((nz([OrdQty],0)-nz([TotalShipQty],0))>0) AND ((tblOrderBook.Status) Not Like "Comp*" And (tblOrderBook.Status) Not Like "Canc*") AND ((tblOrderBook.Ship_Comp)<>True))
ORDER BY tblOrderBook.PL, tblOrderBook.Cur_Prom, tblOrderBook.Item_Number, tblOrderBook.OrderNum;

 

[PHV]

Is by chance qryShipCrosstab_1 a CrossTab query using [Forms]![frmShipRequire]![EndDate] ?
If it's the case you have to define it as parameter:
PARAMETERS [Forms]![frmShipRequire]![EndDate] DateTime;
TRANSFORM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jw45]

No... the crosstab is not using the [Forms]![frmShipRequire]![EndDate] value. However, the issue is related to the crosstab. If I remove the crosstab query the main query works fine and referenced the field on the form as expected.


Here is the SQL from the crosstab

TRANSFORM Sum(tblShip.ShipQty) AS SumOfShipQty
SELECT tblShip.OrderNum, Sum(tblShip.ShipQty) AS TotalShipQty FROM tblShip GROUP BY tblShip.OrderNum
PIVOT Format([ShipDate],"yyyy");

 

[PHV]

Have you tried my suggestion ? (should resolve your issue)

 

[jw45]

Thanks PHV.

Not sure what I did by using the PARAMETERS statement but it worked. Guess I need to do some research.