RichShare
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
ASKER
It's based upon a table. I understand what you are saying and will have a bash at this over the weekend.
Thank you.
Thank you.
=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