Solved

DLookUP Syntax on MS Access

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bind Combobox 4 29
Help writing a query 6 71
Newbie needs help printing from a form. 10 19
format date field on certain entries 8 27
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now