Hello "Shell".
Shell said:
Would you please tell me the problem of the following:
DLookUp("OrderQty","[Current_Purchasing_Order1]",
"[Current_Purchasing_Order1].PN='"& Stock.PN &"' And
[Current_Purchasing_Order1].IssueDate='Dmin("IssueDate",
"[Current_Purchasing_Order1]",
"[Current_Purchasing_Order1].PN='"& Stock.PN &"'")'")
The problem is the quotes inside the quotes.
You could rewrite the expression as
DLookup("OrderQty","Current_Purchasing_Order1","PN='" & Stock.PN &
"' And IssueDate=DMin(""IssueDate"",""Current_Purchasing_Order1"",
""PN='""" & Stock.PN & "')")
But I guess this will be very very slow (if it works at all).
I suggest to create the following parameter query (qryFirstOrderQty):
PARAMETERS StockPN Text( 255 );
SELECT TOP 1 Current_Purchasing_Order1.OrderQty
FROM Current_Purchasing_Order1
WHERE Current_Purchasing_Order1.PN=[StockPN]
ORDER BY Current_Purchasing_Order1.IssueDate;
Then build a VBA function in a standard module using code like this
(you need a reference to the dao library for this):
Public Function GetOrderQtyFirstIssue(PN as String) As Long
With CurrentDb.QueryDefs("qryFirstOrderQty")
.Parameters("StockPN") = PN
With .OpenRecordset(dbOpenForwardOnly)
If Not .EOF Then GetOrderQtyFirstIssue = Nz(!OrderQty, 0)
.Close
End WIth
.Close
End With
End Function
Then use this Function instead of DLookup:
GetOrderQtyFirstIssue(Stock.PN)
This will be much faster and isn't too complicated to use.
