If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Query to conditionally handle duplicates
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 |
#3
|
|||
|
|||
Query to conditionally handle duplicates
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. |
Thread Tools | |
Display Modes | |
|
|