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.