Get field value from related table

[Tom Schoffelen]

Hi,

I am working with Access 2010 and I want to get a field value from an related table and put it into an text field in an report. I tried something like DLookup, but I get this error: #Type! (I am working with the dutch version).

Please give me another, working solution and instructions/code.

[Stewart Ross]

Sorry, but as you have posted no details of the field you are trying to look up, the table or query in which it is located, and the WHERE clause you are trying we can't help you. You should at least have posted the exact call you are using for the DLookup that fails!

Dlookup, like all the domain functions, is straightforward to use but it must be called in the correct format, of which this is just an example:

=DLookup("[YourFieldName]", "[YourTableName]", "somefield=somecriterion")

-Stewart

[Tom Schoffelen]

I am making an database to collect my school points. I have an table that is called subjects and a table ths is called tests. The subjects table has the full name of the subject, and in an report of the table tests, i want to get the full subject name, not just the id.

I tried it again with your code:

=DLookUp("[Name]";"[Subjects]";"[Subject-Id]="+[Subject])

But he gives again this error: #Type!

[Stewart Ross]

Not surprised - the arguments to DLookup must be separated by commas for a start, and you should use the ampersand operator to concatenate the value concerned onto the where-part string:

=DLookUp("[Name]", "[Subjects]", "[Subject-Id]=" & [Subject]

This should work OK if the [Subject] field is a number. If it is a text or alphanumeric value, you will need to enclose it in single quotes, like this:

=DLookUp("[Name]", "[Subjects]", "[Subject-Id] = '" & [Subject]& "'"

-Stewart

[Tom Schoffelen]

If I use comma's,he says that there is an syntax error.

Maybe I am typing it on the wrong place?I added a screenshot.

[Stewart Ross]

Sorry, but I can't tell from the screenshot - it's not got the resolution to let me see where you are typing this.

If you are using an unbound textbox then the property into which you type the =Dlookup expression is the control source for the textbox. As I have said, commas are the separators. Here is an extract from the MS Help entry for DLookup:

You can also use the DLookup function in an expression in a calculated control on a form or report if the field that you need to display isn't in the record source on which your form or report is based. For example, suppose you have an Order Details form based on an Order Details table with a text box called ProductID that displays the ProductID field. To look up ProductName from a Products table based on the value in the text box, you could create another text box and set its ControlSource property to the following expression:

=DLookup("[ProductName]", "Products", "[ProductID] =" _
& Forms![Order Details]!ProductID)

-Stewart

[Tom Schoffelen]

Sorry, but I can't tell from the screenshot - it's not got the resolution to let me see where you are typing this.

If you are using an unbound textbox then the property into which you type the =Dlookup expression is the control source for the textbox. As I have said, commas are the separators. Here is an extract from the MS Help entry for DLookup:



-Stewart

Thank you very much. It works now on the way I want.