Query to conditionally handle duplicates
in [Queries]
|
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. |