Prev: Count by short date and group by agent name
Next: Building/Storing a passthrough:How to assign connection stringand make passthrough
From: bicyclops on 21 Apr 2010 16:54
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.
From: orange via AccessMonster.com on 22 Apr 2010 08:49
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

From: Daryl S on 27 Apr 2010 12:45
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.
 | 
Pages: 1
Prev: Count by short date and group by agent name
Next: Building/Storing a passthrough:How to assign connection stringand make passthrough