Query to conditionally handle duplicates

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.

bicyclops wrote:
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.
We need to see the fields in the query. Do you really have a field called
[PartNumID field]?

--
Message posted via http://www.accessmonster.com

Bicyclops -

The DLookup needs to evaluate the LinkPN outside of the double quotes, and
must reference a field from table in the query that is not the QryPNMultiple
query. It will look something like this if the LinkPN field is text:

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

Or like this if the LinkPN is a number:
DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = " &
[tablename].[LinkPN])

Is the [PartNumID field] supposed to be [PartNumID]?

--
Daryl S


"bicyclops" wrote:

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.