Solved

DLookUP Syntax on MS Access

Posted on 2011-03-11
5
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 85

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 85
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

622 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