Solved

DLookUP Syntax on MS Access

Posted on 2011-03-11
5
394 Views
Last Modified: 2012-05-11
I have an Access report that has an Assembly Reference field, for which I would like show the appropriate Part Description.  The Assrembly Reference field is a look up from a table of Part Numbers and Part Descriptions.

Going through the MS Help file it looks like I need a DLOOKUP function but I can't seem to get the syntax right.  I suspect the use of the word FORMS isn't going to help given I am applying it to a Report!  MS Help doesn't seem to consider this.

The syntax I have used is:

=DLookup ("[Field I want to return]", "Table in which return field is found", "[Field in return table that is to be compared to Assembly Ref field] = " & Forms![Assembly Ref Table]!Assembly ref field)

I hope this makes sense to someone?  I am an advanced user only!
0
Comment
Question by:RichShare
  • 2
  • 2
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35107965
In most cases you're better off include that [Assembly Reference] table in the query that drives your report. You can "join" that table with your other table on the [Assembly ref field], and then include the [Field I want to return] directly in your report's query. You can then use that field as needed.

0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35108023
Your "[Field in return table that is to be compared to Assembly Ref field]" might be a text not a number, so you may try to use the syntax below:

=DLookup ("[Field I want to return]", "Table in which return field is found", "[Field in return table that is to be compared to Assembly Ref field] ='" & Forms![Assembly Ref Table]![Assembly ref field] & "'")


Sincerely,
Ed
0
 

Author Comment

by:RichShare
ID: 35108577
Hi Guys.  I tried the DLOOKUP reference but it came back with NAME? error.  Sorry LSMCOnsulting, I am not quit sure how to change the query to achieve the same thing.  I would appreciate some assistance if you could spare it.
0
 
LVL 84
ID: 35109099
What is your Report based on now? A Table or a Query?

If it's a Table, then you can build a Query that you can use for that Report instead. To do that:

Create a new query. Add the Table that's already being used for the report to the Query grid. Also add the "part numbers and part descriptions" table.

"Link" those two tables - drag the fields that relate Table1 and drop it onto the field in Table2 where that relationship resides. I'm not sure how your tables are related, so I can't really comment about that.

Once you're satisfied with the results, save the query and use as the Recordsource for your report.
0
 

Author Comment

by:RichShare
ID: 35109124
It's based upon a table.  I understand what you are saying and will have a bash at this over the weekend.

Thank you.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question