Solved

DLookUP Syntax on MS Access

Posted on 2011-03-11
5
395 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 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Linking Master Child Fields 2 26
Parameter Query 33 52
Sharepoint list to Access database 9 51
access query - filter field for particular number format 3 30
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

733 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