In my DB, I have the following tables.
Table Name: “Products” Product ID (PK) Product Name
Table Name: “Unit” UnitID (PK) UnitName
Table Name: “Price” PriceID (PK) UnitID (FK of UnitID) Product ID (FK of Products table) Unit Price Standard Cost QtyPerUnit DateOfEntry
I used the tables above to create a query named “Products Extended” with the following fields:
Product ID Product Name UnitID Unit Price QtyPerUnit DateOfEntry Sales Price ([QtyPerUnit]*[Unit Price]) Cost Price ([QtyPerUnit]*[Standard Cost])
Now in preparing the invoice, I use a DlookUp function to Search the “Sales Price” After I must have entered “Product ID” and selected the “UnitID” respectively.
The problem I have is in the function; I want it to look for the Sales price following this order (Let me write the code in SQL for better understanding)
SELECT [Product Details].[Sales Price]
FROM [Products Extended]
WHERE [Products Extended].[Product ID] = Forms.[Invoice Detail].[Product ID] And [Products Extended].UnitID = Forms.[Invoice Details].UnitID And [Products Extended].[DateOfEntry] Is most recent;
Most recent date, I don’t know how to write that in SQL but that is what I want the DLookUp Function to evaluate before returning the “Sales Price”
Thanks in advance for your assistance.