• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

DLookUP Syntax on MS Access

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
RichShare
Asked:
RichShare
  • 2
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
MINDSUPERBCommented:
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
 
RichShareAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
RichShareAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now