I have two tables: "PartNumberX" and "MasterPartTable". My end goal is to extract certain fields out of the "MasterPartTable" using queries to get a list of unique part numbers ("MasterPartTable" has duplicates). I was able to successfully get all the fields out that I wanted except for one using the
SET PartNumberX.[Part Name] = DLookUp("[MasterPartTable].[Part_Name]","MasterPartTable","MasterPartTable.Part_Number = PartNumberX.Part_Number");
UPDATE MasterpartsTable INNER JOIN PartNumberX ON MasterpartsTable.Part_Number=
SET PartNumberX.[Part Name] = [MasterpartsTable].[Part_Name];
DLookup criteria option ...
"MasterPartTable.Part_Number = PartNumberX.Part_Number"
... references the name of a field (
PartNumberX.Part_Number). You need the field's value there instead of its name.
SELECT query to work out the logic and syntax. After you have that returning the correct data, you can adapt as an
Part_Number is a numeric datatype ...
SELECT [Part Name], Part_Number, DLookUp( "Part_Name", "MasterPartTable", "Part_Number = " & Part_Number ) AS master_part_name FROM PartNumberX;
Part_Number is text datatype, include quotes ...
"Part_Number = '" & Part_Number & "'"
However, I suspect you could avoid
DLookup by joining those two tables. If this
SELECT query aligns the data correctly, convert it to an
SELECT p.[Part Name], p.Part_Number, m.Part_Number, m.Part_Name FROM PartNumberX AS p INNER JOIN MasterPartTable AS m ON p.Part_Number = m.Part_Number;
UPDATE based on
INNER JOIN could be noticeably faster than the
DLookup version, especially if the join field (
Part_Number) is indexed.