using dlookup in a query

[bicyclops]

I'm creating a Bill of Materials using a one-to-many relationship table
between InternalPN and ExternalPN. So the query sometimes returns more than
one record when an InternalPN is specified, because there are multiple
External PN's. I would like to alert the user to this in my query by
substituting the word "Multiple" for the ExternalPN.

I can find multiples easily enough with the Query Wizard & so have created a
separate query called QryPNMultiple.
I'm trying to refer to that query in a dlookup statement in my BOM query.
I've tried this:
Expr1: DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = LinkPN")

Everything works but the criteria. Keep getting errors about 'Access cannot
find field LinkPN' but I know it's there. Am I even going about this the best
way?

Thanks in advance.
SQL pasted below for reference (although I use the Query builder)

SELECT TblBOMDetails.LineItem, TblBOMDetails.ParentBOMID,
TblBOMDetails.BOMDetailID, TblBOMDetails.PartNumID, TblBOMDetails.ChildBOMID,
[PartPrefix] & "-" & Format([Part#Suffix],"0000") AS [ItemPart#],
TblBOMDetails.Qty, TblBOMDetails.VendorPNID, TblBOMDetails.Refdes,
[TblPart#].Description, TblPartPrefix.[Prefix Description],
TblMFRPN.MFRPNPart, TblMFR.MFRMfrName, [TblPN-MPNDetails].PartNumID AS
LinkPN, DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = LinkPN")
AS Expr1
FROM TblMFR RIGHT JOIN (TblMFRPN RIGHT JOIN ((TblPartPrefix RIGHT JOIN
([TblPart#] RIGHT JOIN TblBOMDetails ON [TblPart#].[Part#ID] =
TblBOMDetails.PartNumID) ON TblPartPrefix.PartPrefixID =
[TblPart#].PartPrefixID) LEFT JOIN [TblPN-MPNDetails] ON [TblPart#].[Part#ID]
= [TblPN-MPNDetails].PartNumID) ON TblMFRPN.MFRPNID =
[TblPN-MPNDetails].MfrPNID) ON TblMFR.MFRID = TblMFRPN.MFRPNMFRID
ORDER BY TblBOMDetails.LineItem;

 

[Stefan Hoffmann]

hi,

Expr1: DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = LinkPN")

LinkPN is a field reference, thus it must be placed outside the
quotation marks:

DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field]=" & [LinkPN])


mfG
--> stefan <--

 

[Daryl S]

Bicyclops -

You need to provide the LinkPN, not include it in quotes. If the LinkPN is
in your BOM query, then it should look like this:

Expr1: DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = " &
BOM.LinkPN)

Or if LinkPN is text rather than numeric, like this:

Expr1: DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = '" &
BOM.LinkPN & "'")