using dlookup in a query

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.
SQL pasted below for reference (although I use the Query builder)

SELECT TblBOMDetails.LineItem, TblBOMDetails.ParentBOMID, 
TblBOMDetails.BOMDetailID, TblBOMDetails.PartNumID, TblBOMDetails.ChildBOMID, 
[PartPrefix] & "-" & Format([Part#Suffix],"0000") AS [ItemPart#], 
TblBOMDetails.Qty, TblBOMDetails.VendorPNID, TblBOMDetails.Refdes, 
[TblPart#].Description, TblPartPrefix.[Prefix Description], 
TblMFRPN.MFRPNPart, TblMFR.MFRMfrName, [TblPN-MPNDetails].PartNumID AS 
LinkPN, DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = LinkPN") 
AS Expr1
FROM TblMFR RIGHT JOIN (TblMFRPN RIGHT JOIN ((TblPartPrefix RIGHT JOIN 
([TblPart#] RIGHT JOIN TblBOMDetails ON [TblPart#].[Part#ID] = 
TblBOMDetails.PartNumID) ON TblPartPrefix.PartPrefixID = 
[TblPart#].PartPrefixID) LEFT JOIN [TblPN-MPNDetails] ON [TblPart#].[Part#ID] 
= [TblPN-MPNDetails].PartNumID) ON TblMFRPN.MFRPNID = 
[TblPN-MPNDetails].MfrPNID) ON TblMFR.MFRID = TblMFRPN.MFRPNMFRID
ORDER BY TblBOMDetails.LineItem;

0
Utf
4/22/2010 12:22:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
1777 Views

Similar Articles

[PageSpeed] 57

hi,

On 22.04.2010 14:22, bicyclops wrote:
> Expr1: DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = LinkPN")
LinkPN is a field reference, thus it must be placed outside the 
quotation marks:

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


mfG
--> stefan <--

0
Stefan
4/22/2010 12:38:08 PM
Bicyclops -

You need to provide the LinkPN, not include it in quotes.  If the LinkPN is 
in your BOM query, then it should look like this:

Expr1: DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = " & 
BOM.LinkPN)

Or if LinkPN is text rather than numeric, like this:

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

-- 
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.
> SQL pasted below for reference (although I use the Query builder)
> 
> SELECT TblBOMDetails.LineItem, TblBOMDetails.ParentBOMID, 
> TblBOMDetails.BOMDetailID, TblBOMDetails.PartNumID, TblBOMDetails.ChildBOMID, 
> [PartPrefix] & "-" & Format([Part#Suffix],"0000") AS [ItemPart#], 
> TblBOMDetails.Qty, TblBOMDetails.VendorPNID, TblBOMDetails.Refdes, 
> [TblPart#].Description, TblPartPrefix.[Prefix Description], 
> TblMFRPN.MFRPNPart, TblMFR.MFRMfrName, [TblPN-MPNDetails].PartNumID AS 
> LinkPN, DLookUp("NumberOfDups","QryPNMultiple","[PartNumID field] = LinkPN") 
> AS Expr1
> FROM TblMFR RIGHT JOIN (TblMFRPN RIGHT JOIN ((TblPartPrefix RIGHT JOIN 
> ([TblPart#] RIGHT JOIN TblBOMDetails ON [TblPart#].[Part#ID] = 
> TblBOMDetails.PartNumID) ON TblPartPrefix.PartPrefixID = 
> [TblPart#].PartPrefixID) LEFT JOIN [TblPN-MPNDetails] ON [TblPart#].[Part#ID] 
> = [TblPN-MPNDetails].PartNumID) ON TblMFRPN.MFRPNID = 
> [TblPN-MPNDetails].MfrPNID) ON TblMFR.MFRID = TblMFRPN.MFRPNMFRID
> ORDER BY TblBOMDetails.LineItem;
> 
0
Utf
4/22/2010 1:15:02 PM
Reply:

Similar Artilces:

UPDATE QUERY not updating data
Hi all, Is there something obvious I should know about or is it plain magic? I've got a Access 2000 file format database in Access 2003. I've got a table called [Jobs] and I'm developing a way to edit a Job. So I've created an identical table called [EditedJob]. After clicking EDIT I clear the table [EditedJob], then populate it with all the details of a current job and open a form frmEditJob which is bound to [EditedJob] table. Now, I am able to change all the details I want and when clicking "save" button I run this code: Dim qryUpdate As QueryDef Set qryUpdat...

How to create multiple subfolders using VBA
Hello and thanks for reading my question. I need some VBA programming help. I have a main folder with subfolders in it. I want to add a new subfolder to each subfolder in the directory. It would look something like this Main Folder Subfolder1 NewSubfolder Subfolder2 NewSubfolder Subfolder3 NewSubfolder Subfolder4 NewSubfolder I was able to use this VBA to do it for one folder, but I need it to look through all Subfolders MkDir ("C:\MainFolder\Subfolder1\Subfolder1.1") How do I look through each subfolder and add a new subfolder under each...

sum tables cells using row and column conditions
Hi, i need to sum the values in a table based on a name match in COLUMN B and a text match in ROW 4 - say for each occurrence of "Jim Smith" in range B7:B505,when "text" appears in range E4:GC4, SUM all cells which will contain number values- so if:- text1 text2 text3 text1 Jim Smith 3 6 2 4 Sue Brown 1 5 1 7 Mark Bosman 2 9 3 6 Jim Smith 5 4 2 3 the result would have ...

very interesting query- please help
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Using the SDK to update customized fields
The SDK provides references to the XSD schema that can be used for writing CREATE or UPDATE statements. How do you go about updating a custom field that you've added to an entity. Specifically, we add a field to the INCIDENT entity using the DEPLOYMENT manager etc. How can i create my XML - what's the tag that I need to use in order to update the custom field (call it testfield - CFStestfield) does the XSD get updated automatically? Is there anyway to do this?? Thanks. Nick Nick, All you need to do is add the appropriate element to the XML. In your example, it would be CFStestf...

Form Error (Query Update)
Hi, I have about 12 people to work on the database and each has own userID and password. Their UserID and Password input and the form link to the query that has the filter on "UserID and Password" for their input. The message said "Can't Update; Query Locked". Is there I do something wrong? I thought the form is the shared for everyone to input based by UserID and Password. Please help. Thanks We aren't there. We can't see how your database is set up. For instance, do you have a split database, with a single "back-end" file on your network an...

Database Query #3
I posted this earlier with no replies. Anyone got any ideas? I am using a database query to retrieve data from another database excel file. The problem I am having is that the column of data which I have contains records which have both text and number formats. When the data transfers, it only brings those records which are number format. Is there any way to make is so that the database query will pull both number and text formats, as I can't easily change it so that it is one or the other? Thanks The following article in Dick Kusleika's web log may help: http://www.di...

Bar Code
I'm opening a new retail business and would like to use the Manufactures bar code on many of my items......but for the life of me I can't figure out how to load the information into my system. I've serched the HELP screens, but no answers. I'm desperate - Can anyone guide me on how to do this? Sincere, thanks to anyone who can help :) Monica If using the UPC as your ILC doesn't work, you can add the UPC codes to the Alias tab of the Item Properties. Then if you're searching for WIDGET you don't need to memorize the UPC codes. HTH, Tom "Monica" ...

Put daily use worksheets in a toolbar in Excel
Hi, I have Excel 2003 with all updates. Seems to me I used to have a custom toolbar in the menubar area with shortcuts to files I used every day - but it was a long time ago. Is there a way to do that? Thanks, Marti Just record a macro that opens 'em, Marti. See: http://www.officearticles.com/excel/record_a_macro_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "Marti" <MartiB@hotmail.com> wrote in message news:erghi19qjuba0d28jqkic1h5uou3b9uj0n@4ax.com... > Hi, > I have Excel 2003 with all updates. Seems to me I used to have a > custom t...

Toolbar buttons (text and bitmaps) are disappearing, when using with MFC CToolBar class
Guys, help me to sovle the following problem I have a MFC SDI application (Vc++ 6.0) ( 2 Threads, UI and data acqusition thread). The UI is having a simple toolbar control with text and bitmap. The problem is once i run this application after doing some gui operation (like invoking some dialog box changing to diffrent view) the toolbar button is getting disappeared. sometimes the fonts of the toolbar button is getting changed or sometimes when i move the mouse over the toolbar, the buttons is slowly going away( only back ground color of rebar is comming. Is there any methods to debug this ki...

HELP!!! How do you create a query crieria for multi values??
Hello all, please hyelp, its an emergency!!! Need to create a query where one of the searches is male or female, how do I allow user to enter both as the search options?? Also how do I convert tix boxes into a query criteria (e.g. serach for all ticked) Thanks so much for your help ...

Dlookup HELP 02-01-08
I am trying to use a DLOOKUP in a report To lookup a value in a stored query from information that is the report. I have a report that gives me a customers parent account name and I am trying to then use that parent account name to look up a comment stored in another query. This is how my formula looks =DLookUp("TXTFIELD","Query","Query.ParentAccount"="ParentAccountFromReport") Any sugesstions? Perhaps something along the lines of =DLookUp("TXTFIELD","Query","ParentAccount=""" & ParentAccountFromReport &a...

Using Popups to select a range of cells.
Hi All I have a small request for help with code. I need a macro to have popups request the first and last cells in a range , and then to select them. Can someone help? Sub test() Dim Rng As Range On Error Resume Next Set Rng = Application.InputBox("Select your range", _ "Mousework", Type:=8) On Error GoTo 0 If Rng Is Nothing Then Exit Sub MsgBox Rng.Address End Sub HTH. Best wishes Harald "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message news:8mYkILA1YlvLFw7Z@chayes.demon.co.uk... > > Hi All > >...

Using Outlook Address Book vice Windows address book
When I right click on an item as select sent to mail receipient it uses my windows address book, vice my Outlook address book. Since I don't want to update both how do I make it default to the Outlook book and contacts? -- Jeff Mu�iz Work is victory. - Ralph Waldo Emerson Open Internet Explorer and select Tools | Internet Options | Programs tab. Set the contact list dropdown to Microsoft Outlook. "Jeff Mu�iz" <jmuniz6@cox.net> wrote in message news:wEUId.20390$B95.3864@lakeread02... > When I right click on an item as select sent to mail receipient it uses my ...

SQL Smartlist uses?
Hi, I am not a GP user but I need to use SQL to retrieve information from it. The information is in a smart list my client created. I used the smartlist builder to find each table & view used to create the data set. I tried to mirror the joins the smartlist builder uses but I don't get the entire data set. The smartlist returns data for 2007 but my query returns data up to 12/31/2006. How can I get the same results the smartlist yields from SQL? Thanks, Ivan Ivan, If this query is against the GL, from what you're saying, it sounds like 2006 has been closed, and 2007 is open...

DO NOT DO UPDATE IF YOU USE BESTBUY OR DELL!!!!
Once you do the update those banks dont show up as available for linking and auto downloads. If anyone knows how to fix this let me know. Bryan It is the choice of the card issuer not to upgrade with Money. Money did not make this decision. cindy "Bastards" <cellphonesebay@gmail.com> wrote in message news:1185226618.281667.320890@k79g2000hse.googlegroups.com... > Once you do the update those banks dont show up as available for > linking and auto downloads. If anyone knows how to fix this let me > know. > > Bryan > On Jul 23, 5:51 pm, "Cindy C...

How do I use Outlook 2003 with the Win2003 built-in POP3 server?
I am running a small network using Win2003 R2 servers and XP Pro workstations. I do not have Exchange and use the built-in POP3 server for email. I am using Outlook 2007 on the workstations to retrieve mail, but my users need to roam. All my users' data is redirected to an NFS share on my servers and I have relocated their pst files to their home folders. This seems to work OK, except for one user. In this case, when she opens Outlook, she gets the initial setup condition and we need to re-enter all her data again. The pst file seems to be intact and her mail is not lost. I h...

Using Access form to assign values of variables in an Excel VBA program
I have created a VBA program for Ms Excel.But,to get desired results, every time, I go into the code to change the values of variables.Is there a way to get the values of variables stored in an Access table? YOu need to do a search for ADO method (ActiveX Data Object). There are plenty of example of code on the web. You need to add two references in your VBA to use the method. from VBA menu tools - References 1) Microsoft Access XX.X object library 2) Microsfot ActiveX Data Objects X.X library Use the latest versio of the two libraries on your PC. You will make a conn...

Display Date from Unbound Field in Form in a Query
Just had an issue that in 10 years of using Access never saw - just wondering if I just never encountered it or what... I have a form running a query, the user enters a date. The report needs to show the date - so the query has a field like this: report date: [Forms!]![frm_report]![txtReportDate] I had a problem exporting to Excel - seems the direct placement in the query led to characters that Excel could not understand. I actually had another date field from the form that I did a date add on, it worked fine. I eventaully put the form reference in a Format, the issue was gone. The...

using multiple conditions
I would like to know how to write the syntax to test for 1. age>44 or 2. Seniority is >9 and by determining the answer to that 3. Arsenic exposure >4.9 4. Cadmium exposure >2.4 If 1. is true or 2. true then I need to check 3. and 4. if 1. is true or 2. is true and 3 and 4 are false then ? if 1 or 2 is false and 3 or 4 is false then something else would I use and or /and condition. The way I wrote my syntax the or works but the and does not. Any clues on how to write the syntax to check for all 4 conditions? This is one solution: ="1 is "&...

Using dial-up w/ citibank
Does credit card validation w/ citibank on a dial-up connection required that I get an Internet Service provider, or does the software simply call an 800 number out the modem when doing credit card authorizations? Thanks - Bob The answer depends on your configuration. I'd be happy to speak with you. I can be reached at 949-629-3002 during the day Pacific Time. Brett "Bob (new user)" wrote: > Does credit card validation w/ citibank on a dial-up connection required that > I get an Internet Service provider, or does the software simply call an 800 > number out t...

Importing Excel and Word using automation
The last time I tried this I got very frustrated. I think I'll probably need to buy a book or something. Can someone recommend such a resource, or any other suitable resource for that matter? Before you go out and buy a book, try reading the Automation documentation that Microsoft provides with their Office applications. The documentation file names vary, depending on the version of Office, but for instance, my Office 2003 documentation is in files named the following (obviously, the root path varies depending on where you installed): E:\Office\OFFICE11\1033\VBAXL10.CHM E:\Office\OFF...

Feasibility of using visual basic express
Hi Is it possible to develop sql server db winform apps using visual basic express? Thanks Regards Yes, The easiest way, have a look to SQL to Linq success Cor "John" <info@nospam.infovis.co.uk> wrote in message news:OV8Ezd3tKHA.4908@TK2MSFTNGP06.phx.gbl... > Hi > > Is it possible to develop sql server db winform apps using visual basic > express? > > Thanks > > Regards > Thanks. Isn't Linq to SQL killed by MS? http://ayende.com/Blog/archive/2008/10/31/microsoft-kills-linq-to-sql.aspx "Cor ...

Excel Query Opens Source
All, I am one of several offices supported by a purchasing group. This purchasing group maintains the status of all purchases in an Excel workbook. They will not open up the workbook for all. I have convinced them to let me create a workbook that queries theirs and returns just my offices purchases. They will have given everyone access required so that the query will work. As long as the exact location is not readily available and all of the data is not available, they are happy. Everything worked great for two weeks, but a week ago when the data is refreshed, the entire source workbook is o...

customer DB email address query
I would like to run a query or chrystal report to pull off all my customers email addresses so I can send one single email to all of them. Does anyone know the best way to do this? Thanks! I have a report for HQ Mgr that will generate a listing of all of your customers within RMS that have an email address assigned. You would use this report (filter so email <> (blank)) then generate and export. Do you have an address where I can send it? -- Jocelyn "zoostation" wrote: > I would like to run a query or chrystal report to pull off all my customers > email addr...